Query Criteria

[Becker]

Is it possible to set criteria for a query that would make it go to the current date except for the hours of midnight to 6am go to the previous date?

[Rabbit]

The Date() function will return the current date. The DateAdd() function will let you subtract and add a chosen interval to a date.

[Becker]

Thanks. That helps for finding the previous date. Now how can I specify times to use it at? I want the current date from 6am to midnight and the previous date (using the datadd() function) for midnight to 6am.

[twinnyfo]

Becker,

If you use:

Now() - Date()

you will get the decimal part of the Date/Time generated by Now().

The "Time" portion of a date is actually expressed in a decimal in the system. 12:00 Midnight = 0.00000; 12:00 Noon = 0.50000. Thus, the hours between Midnight and 6 Am would be <= 0.25.

So, what you want to evaluate your date/time to be is somewhat similar to this:

WHERE [Your Date/Time Field] = 
    IIf(Now()-Date()<=0.25,Date()-1,Date())

Hope this hepps!

[Becker]

That works perfectly. Thank you.

[twinnyfo]

Glad I could help! Let us know if you have additional questions.

[NeoPa]

TimeValue(Now())

This is a simpler way of getting to the time-only portion of Now().

Date() == DateValue(Now())

Thus, the simplest and most straightforward way of returning the date specified would be :

DateValue(DateAdd("h",-6,Now()))

This reflects going back six hours from the current time and taking the date part of that value.

[twinnyfo]

NeoPa,

Thank for sharing the two functions above. I didn't know they existed... Now I do!

[NeoPa]

TwinnyFo:
I didn't know they existed...

You know, I maybe psychic, but I'd guessed that already :-D

Of course you're welcome. Sharing knowledge with those I know will pass it on is always even more pleasing and rewarding.