Set filter in Access for DateTime - Yesterday

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
GPSPOWAsked:
Who is Participating?
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try double quotes around the d
Dateadd("d",-1,Date())
0
 
GPSPOWAuthor Commented:
Did not work.

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

glen
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
is there Time in your date field ?

And how are you using this?   Can  you post the SQL or query grid?
0
 
GPSPOWAuthor Commented:
I am using MS Access 2010.

The field AdmitDateTime has a DateTime format:  1/1/2014 12:14:00 AM
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
GPSPOWAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.