Hours

[Doc94]

How would I return a time interval based not on 24 hour clock but rather based on 11pm one day to 11 pm the next day?
I am able to sort my records by hour but I need to then count them by the time interval of 11pm-7am(which of course crosses over the midnight time period into a new day) 7am-3pm, and 3pm-11pm. And I need to do this the next day.
Right now the report is based on times entered in a form but that can change also.

 

[KenReay]

Hi

In your query on which the report is based make a 'calculated' column eg ElapsedateDiff("h",Time1,Time2)

see help for exact syntax of DateDiff() which will explain to you what is going on, but basically, despite its name the DateDiff function will return a lon which is the diference between two date/time fields, in units as specified by you (eg here we have "h" for hours), but you could have say "m" for minutes, as I say see help on DateDiff to see all the possibilities, having made the column, include it on your report and sort on it

Regards

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

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

UK

 

[scriverb]

KenReay is correct in recommending the use of DateDiff function to calculate the hours between two times. But, you really should include the Date and Time in this field because that is the only way that you are going to know if the timeframe rolls over to the next day. It is obvious for a 11:00 pm thru 7:00 am as you are going from PM to AM but what about 11:00 pm to 11::15 pm. Is this 15 minutes or 24 hrs 15 minutes. You should always store the date and time in a field so that you have the date portion of the date/time field. Then the DateDiff function work for you in all situations. By using the Now() function rather than Date() function you will store both the date and time in your date/time field.

By doing this you also open up another pitfall. When a date/time is stored you cannot use the "equals" operator to compare to the begin or ending date of a timeframe. You also have to be careful about using the Between operator. You see the Begin Date may be 8/21/2003 11:00 pm and when you make a comparison using the equals or between operators and your beginning date if 8/21/2003 it may not include this record because of the time value which is included in your date/time field.

Post back with any questions that you may have.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???