Selecting Yesterdays Records using DateTime Field

Question

I'm trying to select from a table all of yesterdays records (not the last 24 hours worth of records). The field in question is defined as DateTime (in Access 2007) and is formatted dd/mm/yyyy hh:mm:ss.

My first thoughts (as per many other Q&A's) would be to take an approach like:

SELECT * FROM someTable WHERE DateAdded = DateAdd("d", -1, Now())

As mentioned though, the fields format means that no results are returned as the condition compares the time value as well.

My next thought was to convert the DateTime fields into dates like so:

SELECT * FROM someTable WHERE cDate(Added) = DateAdd("d", -1, cDate(Now()))

Once again, no records are returned though.

If I run the following queries though, both return 29/10/2014:

--SELECT yesterdays date
SELECT DateAdd("d", -1, cDate(Now())) --Returns 29/10/2014

--SELECT the date of the last record
SELECT TOP 1 cDate(Added) FROM someTable ORDER BY Added DESC --Returns 29/10/2014

So my question is how do you select yesterdays records?

score 2 · Accepted Answer · answered Oct 30 '14 at 14:18

Your WHERE clause needs to include all Date/Time values from yesterday (i.e. the 24-hour period between yesterday at midnight and today at midnight), so you need to do

SELECT * 
FROM someTable
WHERE DateAdded >= DateAdd("d", -1, Date())
    AND DateAdded < Date()

Selecting Yesterdays Records using DateTime Field

1 Answers1