Time formatting

[Guest]

Guys,

I'm designing a Billing Analyst for our phone bills and as part of that it
looks at call duration. One query that I do is a total duration for each
line/number. Problem I'm hitting is if the duration is over 24 hours in
length then it will not display correctly. Rather than saying something like
(hh:mm:ss) 30:12:05 it will say 06:12:05. Which in a sense is correct as it
is 1 day 6 hours 12 minutes and 5 seconds. But that doesn't help me at all.

Is there a way that I can get it to keep going so that it will display
30:12:05 or even 105:12:05?

Can anyone help?

 

[Douglas J. Steele]

The Date data type in Access is intended to be used as a timestamp, not as a
duration. Under the covers, it's actually an 8 byte floating point number,
where the integer part represents the date as the number of days relative to
30 Dec, 1899, and the decimal part represents the time as a fraction of a
day. If you were to format the variable that's returning 30:12:05 to show
date as well, you'd see it's actually saying 06:12:05 on 31 Dec, 1899.

The recommended approach for dealing with durations is to figure out the
resolution your want (minutes, seconds, etc), and to store the durations in
those units (eg. a Long Integer containing the total number of seconds). You
can then manipulate the durations to your heart's content. Write functions
to convert between Long Integer to hh:mm:ss to make things easier.