What happened YESTERDAY - date/time

[Annette]

I would like to have a query give me results of "what happened
yesterday'. So at 5:00am this morning, I would like a query to run and
give me results from a table of everything that happened the day
before -- from midnight to midnight.

So if it is 4/11/2008, and the job is run at 5:00am on 4/11, the query
would return everything that happened from 04/10/2008 at 12:00:01
through 04/10/2008 11:59:99.

 

[john.fuller]

I would like to have a query give me results of "what happened
yesterday'. So at 5:00am this morning, I would like a query to run and
give me results from a table of everything that happened the day
before -- from midnight  to midnight.

So if it is 4/11/2008, and the job is run at 5:00am on 4/11, the query
would return everything that happened from 04/10/2008 at 12:00:01
through 04/10/2008 11:59:99.

I would think using the criteria of "Date()-1" would work. If the
field you're trying to use contains times as well as dates, you may
have to use ">Date()-2 and <Date()-1".

 

[Klatuu]

WHERE Format([TheDate],"yyyymmdd") = Format(DateAdd("d",-1,Date),"yyyymmdd")

This removes the time components from the date and compares the date field
in your table ([TheDate]), to yesterday (DateAdd("d",-1,Date)) and only
returns records from yesterday. Now, the question is, is this a 7 day
process or do you need to check Friday on Monday? And what if Friday or
Monday is a holiday, or you are running it on Wednesday and Tuesday was a
holiday?

If you need help with that, I have some functions I can share that will do
that sort of calculation.

 

[KARL DEWEY]

Add a calculated field in the design view of the query like this --
Date_Only: DateValue([YourDateTimeField])
Use criteria Date()-1

 

[Rick Brandt]

I would like to have a query give me results of "what happened
yesterday'. So at 5:00am this morning, I would like a query to run and
give me results from a table of everything that happened the day
before -- from midnight to midnight.

So if it is 4/11/2008, and the job is run at 5:00am on 4/11, the query
would return everything that happened from 04/10/2008 at 12:00:01
through 04/10/2008 11:59:99.

If your date field is indexed and you want the query to be able to utilize
that index then you don't want to apply criteria to an expression. That
forces a scan of all rows (no index used).

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

Notice I am using expressions on the right side of the comparison, but they
are expressions that do not include any fields so they only need to be
evaluated once (not for every row) and the left side of each comparison is a
field name only. That allows the query engine to make use of any index that
might exist on that field.

This might not make any practical difference on smaller (local) tables, but
it is a good habit to get into. On larger (especially networked) tables the
difference can be huge.