[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Problem with DateAdd Function in Query

Posted on 2011-10-13
Medium Priority
399 Views
Today is Oct. 13, 2011 (10/13/11) -

1. The following query will not return any records for 10/12/11 although records do exist:

SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
FROM tblProd2

The following queries will work:

SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
FROM tblProd2
WHERE (tblProd2.ProdDate)<DateAdd("d",-1,Now());   'Returns all < 10/12/11

SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
FROM tblProd2
WHERE (tblProd2.ProdDate)>DateAdd("d",-1,Now());  'Returns all > 10/12/11

Attempts to use >= or <= in the criteria will not work.

I can use the following to return all records for 10/12/11:

SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
FROM tblProd2
WHERE (tblProd2.ProdDate)>DateAdd("d",-2,Now()) AND (tblProd2.ProdDate) < Now());

Why doesn't     WHERE (tblProd2.ProdDate)=DateAdd("d",-1,Now());    return records for 10/12/11?
0
Question by:Tim313

LVL 120

Expert Comment

ID: 36963387

SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
FROM tblProd2
0

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 36963391
the Now() function includes Time values..
0

LVL 31

Expert Comment

ID: 36963902
Why doesn't     WHERE (tblProd2.ProdDate)=DateAdd("d",-1,Now());    return records for 10/12/11?

Using Date() Produces values like 10/12/11
Using Now() Produces values like 10/12/11 09:10:00
Which are not equal
0

LVL 26

Expert Comment

ID: 36965246
Dates are the funnest thing there is!
The previous posters have noted the beginning of the fun--that Date() and Now() do not return the same thing.
One returns 13-Oct-2011 12:00:00 AM and the other returns 13-Oct-2011 02:37:00 PM

That's the start
If tblProd2.ProdDate is filled with values that were based on Now(), they will ALSO have times.
So you can have stuff like Format(tblProd2.ProdDate, "dd-mmm-yyyy") that will return stuff showing today's date.
But it may still fail tblProd2.ProdDate < #13-Oct-2012# because the time part is still there!

Access ALWAYS stores a time.  If you don't specify one (you entered a date by hand -- 13-Oct-2011 or used Date()) then the time is midnight--12:00:00 AM
If you use Now() or specify a time, then the specified time gets stored.

Format() just changes what you SEE for string values, not the actual value of the datetime.
Moreover, your regional settings influence how string-literal times are interpreted.
01/06/11 can be the 6th of January or the 1st of June depending on the settings.

Which is an EXTREMELY good reason NOT to use that format.
ISO 8601 format -- 2011-10-13 -- never gets misinterpreted.
Providing you are always using english, medium date never goes wrong either -- 13-Oct-11

Danger, Will Robinson!  Never forget the time.  It's ALWAYS there

This may play

SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
FROM tblProd2
WHERE (tblProd2.ProdDate) >=CDate(Date()-1 & " 11:59:00 PM")
0

LVL 52

Expert Comment

ID: 36969297
It might be a more logical approach to use DateDiff:

WHERE DateDiff("d", tblProd2.ProdDate, Date()) = 1

However, should you have an index on ProdDate, this will not be used.

/gustav
0

Author Closing Comment

ID: 36985371
Thanks to everyone. Since all replies were the same, capricorn1 gets the points for first response.
0

## Featured Post

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
###### Suggested Courses
Course of the Month19 days, 9 hours left to enroll