Hours and Minutes 1

[puforee]

I have a field in my table that stores time...it is formatted for Short Time. The issue...this field shows how long it takes to do a task. Sometimes it takes over 24 hours. The field errors out if I enter more than 23:59. I would like the field to stay formatted to HH:MM but I need to exceed 24 hours.

Can anyone help?

 

[Golom]

Simple answer ... don't use a DateTime field for this. Such fields record a point in time and the Time portion is represented as a fraction of one day. Once you exceed one day then this data type rolls over to the next day.

You are attempting to record an elapsed time or time interval. Use a fieldtype like Long and store the elapsed time as minutes (assuming that you don't care about seconds). You can then easily format that in a query with

[blue]Format(Int(ElapsedTime/60), "00") & ":" & Format((ElapsedTime Mod 60), "00")[/blue]

 

[lionelhill]

... or as a tiny variation on Golom's approach, depending on application, I might store the elapsed time in units of days (same as Access uses for DateTime), as a double, and format appropriately as Golom did. Disadvantage: the formatting needs rounding (I'd add a user-defined function so I don't need to type it out everywhere). Advantage: you can mix your elapsed time with genuine dates and the arithmetic will work correctly.