Query between yesterday 6AM and today 6AM

[mikelev]

I have a report based on a query. I have 'Date()' in the criteria field which works great for selecting records created today.

Can someone provide an alternate criteria that will select between yesterday 6AM and today 6AM (or the current time)?


Cheers,

 

[BobJacksonNCI]

Assuming your field contains date and time, this should do it:

between (Date()-1 & " " & #6:00AM#) and (Date() & " " & #6:00AM#)

HTH,
Bob

 

[scriverb]

Try this:

WHERE [[i][red]yourdatefield[/red][/i]] between ((Date()-1) + TimeSerial(18,0,0)) and (Date() + TimeSerial(6,0,0))

If you want to use the current time for the second date/time value then use the following:

WHERE [[i][red]yourdatefield[/red][/i]] between ((Date()-1) + TimeSerial(18,0,0)) and (Date()+ Time())

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Sorry, I misread that one. Forget the PM part. Change it to AM.

WHERE [yourdatefield] between ((Date()-1) + TimeSerial(6,0,0)) and (Date() + TimeSerial(6,0,0))

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[mikelev]

between (Date()-1 & " " & #6:00AM#) and (Date() & " " & #6:00AM#)

Works perfect thanks to you both!

 

[BobJacksonNCI]

Every now and again, the Hawkeyes actually beat the Spartans...

(In this case, it's more of a tie.
Shades of MSU versus Notre Dame many years ago.)

Bob

 

[scriverb]

Off subject here, but I was there 10-10 what a whimp Ara was. Wouldn't go for it at the end and let the clock run out. Notre Dame fans were hanging their heads that day.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]