querying time/date

[johnlatona123]

hi!

how does one go about subtracting an [start time] from and [end time] to get a total of the difference in a usable format such as minutes?

i keep getting wild decimals when i try to do this.

please help!

 

[pbaldy]

The decimals are due to how Access stores date/time values.

Try the DateDiff() function. More info on the available arguments in Help. Hopefully you have a date component in your value, or times across midnight will require some manipulation.

 

[johnlatona123]

hi!

thanks for the reply, i was able to get the difference in time to display.

my next issue is that i to set my critera to do the following:

i will have a seperate query for each of these 3 critera.

1) only look at records between 4:00:01pm and 2:30:00am on monday through thursday

2) only look at records between 5:30:01am and 4:00:00pm on monday through thursday

3) only look at records between 6:00:01am and 6:30:00pm on friday through sunday

the field in question is set up as a "general date field" and displays like 12/27/2011 1:03:41 PM.

thanks again in advance!

 

[johnlatona123]

anyone able to help with this?

 

[pbaldy]

You posted at 4am my time, so following up 4 hours later is a little over the top.

Try this type of thing:

SELECT...
FROM...
WHERE (TimeValue(FieldName) > #16:00:00# OR TimeValue(FieldName) < #2:30:00#) AND WeekDay(FieldName) IN(2,3,4,5)

 

[johnlatona123]

sorry to be buggy about it, need it in a hurry.

also thanks for the reply! that seems to have worked

 

[pbaldy]

Happy to help.