Query between two times

[msuguy71]

Hello, I need to query a database for records that fall between 23:00 and 06:00 and I used the following in the query field criteria box:

Between #11:00:00 PM# And #6:00:00 AM#

This returned records between 06:00 and 23:00.
In other words I got the records that occurred during the day but I need the records that occurred during the night. What am I missing?

Thanks,
Rick

 

[KenReay]

Hi

In a word the Date

Date/Time fields hold both the date and the time, what you are actually asking for is (say):

Between #2004/11/16 11:00:00 PM# And #2004/11/17 6:00:00 AM#


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[msuguy71]

Ok, that makes sense. How do I get it to read each individual day though? This is for a whole month. I already as them for a start and end date. How do I make it read each individual day between those days.

 

[PHV]

WHERE (([DateTime Field]-Int([DateTime Field])>=#23:00:00#)
Or ([DateTime Field]-Int([DateTime Field])<=#06:00:00#))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244