calculate times in a table

Question

Hi All

I have a database with a lot of times. Example Times a truck arrived, Time loading started and ended.

I need to calculate the time between the start loading and end loading. If the time is on the same date, there is no issue, but if the time roll from Monday 23:00 to Tuesday 02:30, I get a lot of garbage.

Is there a way I can calculate the times correctly? I specifically need this to be performed using a SQL statement and not a query, but a query will be most helpful as well.

Thanks

Deon

Answer 1

An SQL statement and a query is the same. 

You can use an old trick in this expression in the SQL:

TimeValue(CDate(1 + [EndTime - [StartTime])) As LoadTime

The output will be a datetime value you can format as you like.

---

Gustav Brock

Answer 2

You need to store the entire datetime, not just the time. If you store the entire thing, time happening over midnight will be easy to compute. A simple DateDiff("interval as string", [StartTime], [EndTime])

Use "n" for minutes; "h" for hours.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals