Solved

Set filter in Access for DateTime - Yesterday

Posted on 2014-01-02
7
1,210 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
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…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

856 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