Solved

Set filter in Access for DateTime - Yesterday

Posted on 2014-01-02
7
1,113 Views
Last Modified: 2014-01-08
What is the syntax in an Access Query to set a criteria filter for a DateTime field for yesterday?

I tried Dateadd('d',-1,Date()) but that does not work.

Thanks

Glen
0
Comment
Question by:GPSPOW
  • 3
  • 3
7 Comments
 
LVL 75
ID: 39752635
Try double quotes around the d
Dateadd("d",-1,Date())
0
 

Author Comment

by:GPSPOW
ID: 39752640
Did not work.

I know if have data for 1/1/2014.

glen
0
 
LVL 75
ID: 39752648
is there Time in your date field ?

And how are you using this?   Can  you post the SQL or query grid?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:GPSPOW
ID: 39752663
I am using MS Access 2010.

The field AdmitDateTime has a DateTime format:  1/1/2014 12:14:00 AM
0
 
LVL 75
ID: 39752685
So,  looks time Time is being stored with date?
In that case, you need to use DateValue([AdmitDateTime]) to extract just the Date - and compare that against Dateadd("d",-1,Date())
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 39752764
To expand on DatabaseMX's recommendation:

Date() return as data with the time set to 00:00 or at midnight.

Date() will return 1/1/2014 00:00:00 AM so it will never equal 1/1/2014 12:14:00 AM.

I deal with it in two ways:

1) (DatabaseMX's recommendation) Strip the time from the date  value which will reset the time  to 00:00 that will match Date()'s time.

2) Compare your Date Field to a range.

[YourDateField] Between #1/1/2014#  and #1/1/2014 12:59:59 PM#

Open in new window


or

[YourDateField] Between Date()-1  and ((Date()-1) + TimeValue("23:59:59"))

Open in new window



or

[YourDateField] < Date  and [YourDateField] >= (Date()-1)

Open in new window


Using your field name and Between

Where [AdmitDateTime] Between (Date()-1)  and ((Date()-1) + TimeValue("23:59:59"))

Open in new window

0
 

Author Closing Comment

by:GPSPOW
ID: 39766318
Thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now