selecting a time range

[andrewvranjes]

Hello!

this forum has helped me big time.

now i have another quesiton.

i want to run a report that selects all jobs from 5pm to 8am. I need to run this report at 8.01am to get all data from the previous night.

I am resonably comfortable with the date functions but time queries elude me.

i have tried the build function, using = hour(17) or hour(18) etc etc

but its not working.

please help!

 

[Jon K]

Try this criteria in the date/time field column in the query grid:-

Between (Date()-1)+CDate("5:00 PM") And Date()+CDate("8:00 AM")

 

[andrewvranjes]

it didtnt work

Hi Jon,

thanks for replying.

no it didnt work. it accepts that syntax but selects no records.

the time field is seperate from the date field, so i have tried some variations of >date()-1 in the date field and >time()-900, but it also selctes no records.

i am thinking of loading crystal reports, any suggestions?

thanks again

 

[Jon K]

I assume both the date field and the time field are date/time fields, i.e. not text fields. Try this in a column, using the correct field names:-

Field: [DateFieldName]+[TimeFieldName]

Show: uncheck

Criteria: Between (Date()-1)+CDate("5:00 PM") And Date()+CDate("8:00 AM")

 

[andrewvranjes]

thanks, didnt work again, but close!

Hi Jon,

thanks for that. it didnt work, but at least i can see the value in trying things other ways.

with the date()-1 it selects no records. i increased the date() and it selects a few records, but not in the time range!

i will attach the file if you can look at it that would be great.

 

[Jon K]

I imported your xls file into the attached DB and created a query using the criteria:-

Field: [LogDate]+[LogTime]
Show: checked
Criteria: Between (Date()-1)+CDate("5:00 PM") And Date()+CDate("8:00 AM")


When the query was run, it returned these two records on my system:-
2/14/2003 12:40:00 AM
2/14/2003 7:55:58 AM


You can run the query in the attached DB to see if it works on your system.


The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose convert and save as a new name when the DB is opened for the first time.

 

[andrewvranjes]

thanks!

thanks!

thats works.

now how do i link this query back to my original database?

its name is workbench. it lives on a sql server, 192.168.10.13.

we use access all the time at work. do you offer some sort of business charageable fixes ?

please advise