Criteria to filter for "yesterday" from general date/time format

[JamieD]

The field that I am trying to filter within is date/time in the following example format:
9/1/2015 10:00:00 AM

I need to filter for "yesterday", which I know should be "date()-1", but for some reason this isn't working. It seems like the solution should be simple, but I'm having difficulties identifying it.

Please help!

 

[Micron]

Your problem is that your field is date/time. Your example will minus one day from Date(), but not when the date value contains a time component. If you don't need the time in the table, I'd eliminate it - it causes issues, as you can see from your situation and one I point out below.

DateAdd("d", -1, [YourDate]) should go back a day, but I don't know how to advise you to use it (=, >, >=, Between, etc.) since I don't know exactly what you are doing. As I've posted in this forum before, date/time fields cause issues with the Between operator and need special handling. If you specify BETWEEN 01/01/2015 AND 01/15/2015, the results will be cut off on the fifteenth at 00:00:00 (i.e. you will not get 1:00 A.M. for example).

 

[JamieD]

I just wanted to throw this out there for future reference.

Since time was part of the data (9/15/2015 8:00:00 AM), I couldn't use "date()-1" because I kept getting zero results. The reason being is with time in this format, technically the date starts at zero and goes to 2400 (essentially). So when I used "Between date()-1 AND date()", my results were all records throughout "yesterday".

@ Micron - Thank you for your suggestion, I really appreciated you taking the time to help!

 

[Micron]

'K, you didn't say you were using the Between operator. In those cases, you use the DateAdd function on the second date and add however many intervals (such as minutes or seconds) you think you need. For example, it would be something like "Between Date1 and DateAdd("n",1439,Date2)" to add 23 hours and 59 minutes to the second date (from the zero hour point).

And no problem - glad to help and like to hear whether or not something I suggest works.