Retrieve date from field with date/time.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to use Date()-2 in the criteria of a query for a field that store
=Now(). Can someone tell me how to format the criteria to do this?
Thanks
 
Raj said:
I want to use Date()-2 in the criteria of a query for a field that
store =Now(). Can someone tell me how to format the criteria to do
this? Thanks
Click to expand...

The easy (but less efficient) way...

WHERE DateValue(YourField) = Date()-2

That method will not be able to take advantage of any index you might have
on the date field. A more complicated method that can utilize an index
is...

WHERE YourField Between Date()-2 and DateAdd("s",-1, Date()-1)

That translates to between 2 days ago at midnight and 1 second before
midnight of the following day.

The key is to leave the thing being tested as just the name of your field.
Then an index can be used regardless of how convoluted the expression for
the test is. As soon as you wrap the thing being tested in an expression
(no matter how simple) then you force the database to scan every row and
make the comparison.
 
Rick,
Thank you!

Rick Brandt said:
The easy (but less efficient) way...

WHERE DateValue(YourField) = Date()-2

That method will not be able to take advantage of any index you might have
on the date field. A more complicated method that can utilize an index
is...

WHERE YourField Between Date()-2 and DateAdd("s",-1, Date()-1)

That translates to between 2 days ago at midnight and 1 second before
midnight of the following day.

The key is to leave the thing being tested as just the name of your field.
Then an index can be used regardless of how convoluted the expression for
the test is. As soon as you wrap the thing being tested in an expression
(no matter how simple) then you force the database to scan every row and
make the comparison.
Click to expand...
 
Back
Top