• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1604
  • Last Modified:

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
0
GPSPOW
Asked:
GPSPOW
  • 3
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
is there Time in your date field ?

And how are you using this?   Can  you post the SQL or query grid?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 MVP, Access and Data Platform)Commented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
GPSPOWAuthor Commented:
Thanks
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now