Date Query not working properly!!

  • Thread starter Thread starter Steven Phillips via AccessMonster.com
  • Start date Start date
S

Steven Phillips via AccessMonster.com

I have a query in which I am trying to calculate yesterdays totals, for
shipments per day. My first query totals everything so I have a month to
date total, then I query from that. I have tried numerous ways of using
(now()-1) and either it does not work or I still get the entire total.
Should this be in the criteria section or am I just going about it the
wrong way?

Thanks,
Steven
 
Try using Date() instead of Now().
Now() is this date and time, so Now() - 1 is this time yesterday.

To get all the records from the first of this month onwards, you could use
this expression in your Criteria:
= Date() - Day(Date()) + 1
Click to expand...

Or to get all records from the beginning of this month until this moment:
Between (Date() - Date(Date()) + 1) And Now()
 
Allen,
Thanks for quick response. When I input the date criteria in as suggested
I got an undefined date expression. To get yesterdays total would it be
Date()-1 ?

Steven
 
If your computer does not recognise Date(), you probably have a reference
problem. See:
http://allenbrowne.com/ser-38.html

Yes, yesterday is Date() - 1, and that will work unless your field has a
time component as well as the date. This typically happens if you used
=Now() as the default value instead of =Date(). In that case you would need
to use:
= Date() - 1 And < Date()
Click to expand...
 
Back
Top