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.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try double quotes around the d
GPSPOWAuthor Commented:
Did not work.

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

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
is there Time in your date field ?

And how are you using this?   Can  you post the SQL or query grid?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

GPSPOWAuthor Commented:
I am using MS Access 2010.

The field AdmitDateTime has a DateTime format:  1/1/2014 12:14:00 AM
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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())
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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


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

Open in new window


[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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.