times query

[ashaikh05]

Hello

I'm trying build a query so i can find out records that occur between two times.

I have a table with a Time field which records the date and time of the occurance. What I need is the any activity that took place after 5:00 pm but before 9:00 the next day.

Thanks

 

[SgtJarrow]

Something like this should get you started....

WHERE (((DatePart("d",[thedate]))=[what day]) AND ((DatePart("h",[thedate]))>=17)) OR (((DatePart("d",[thedate]))=DateAdd("d",1,[what day])) AND ((DatePart("h",[thedate]))<=9));

Replace [thedate] with [your date field name here]
Replace [what day] with Day([the date field you are comparing for]) OR [whatever prompt you want to ask the users]

To describe a bit, in my example I quickly created, when the query is opened a prompt is displayed asking "what day". I enter 20 and click OK. I get all sample records I created that occured on 3/20/2007 after 5pm and occured on 3/21/2007 before 9 am.

You may need to tweak it a bit for your scenario. If you can't figure it out, please post back what you have tried, including code and sample data you are working with.

Hope this helps.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SkipVought]

...or

Where [thedate] Between CDate(int([thedate])+CDate([Start])) and CDate(int([thedate])+CDate([End]));

assuming that the [Start] & [End] times are BOTH in the same day.

Skip,

_[red][/red]