Display elapsed time greater than 24 hours

[mikelee101]

Greetings,
I have a table of phone calls, number called, duration of call, etc. I
have a query which counts the calls and sums the duration over a given
time period, but I'm having some trouble with the output. If the total
time exceeds 24 hours, the query doesn't display it correctly. For
example, there is a format in Excel like this:

[h]:mm:ss

which would show 135 hours, 48 minutes and 26 seconds as

135:48:26.

I've tried the Access standard formats (which appear to interpret
everything as a time of day as opposed to an elapsed time) and the
following custom formats:

h:nn:ss
hh:nn:ss
hhh:nn:ss
hhhh:nn:ss
[h]:nn:ss
!hhh:nn:ss

None of which worked when the elapsed time exceeded 24 hours.

I also tried:

d:hh:nn:ss

to see if I'd get

5:15:48:26

but that didn't give me the expected result either.


If anyone has any ideas on anything else I could try, I'd really
appreciate it.

Thanks to all.

Mike

 

[John Vinson]

I've tried the Access standard formats (which appear to interpret
everything as a time of day as opposed to an elapsed time)

That's what Access date/time fields ARE: precise moments in time.
They're not suitable for durations.

I'd suggest storing your durations as Long Integer counts of minutes.
You can format them for display with an expression

[Duration] \ 60 & Format([Duration] MOD 60, "00")


John W. Vinson[MVP]

 

[Douglas J. Steele]

Actually, you'd want

[Duration] \ 60 & ":" & Format([Duration] MOD 60, "00")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I've tried the Access standard formats (which appear to interpret
everything as a time of day as opposed to an elapsed time)

That's what Access date/time fields ARE: precise moments in time.
They're not suitable for durations.

I'd suggest storing your durations as Long Integer counts of minutes.
You can format them for display with an expression

[Duration] \ 60 & Format([Duration] MOD 60, "00")


John W. Vinson[MVP]