Solved

Set filter in Access for DateTime - Yesterday

Posted on 2014-01-02
7
1,178 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
My experience with Windows 10 over a one year period and suggestions for smooth operation
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

777 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