add hours, minutes and seconds

[gracegrenier]

Hello,
I have a database that collects data on the length of calls in hh:nn:ss format. I need to be able to add these together in different queries giving me monthly and ytd totals but Access doesn't go past 24 hours. Does anyone know how I can get accurate totals?
gg

 

[jdraw]

see this article
http://office.microsoft.com/en-us/a...ct-from-date-and-time-values-HP001098485.aspx

 

[Beetle]

Convert to seconds, Sum the seconds, convert back to hh:nn:ss.

As long as no single record value exceeds 23:59:59 you can convert to seconds (and Sum) with (example field named CallTime);

Sum(TimeValue([CallTime])*86400)

If any single record value can exceed 23:59:59 then you would need to convert to total seconds with something like;

Sum((Left([CallTime],2)*3600)+(Mid([CallTime],4,2)*60)+(Mid([CallTime],7,2)))

Then to convert back to hh:nn:ss you have a couple options.

1)You can use this;

Format(([YourSumField]/86400),"hh:nn:ss")

but since Time formatting uses a 24 hour modulus, it will roll back to zero after 23 hours, so;

29:50:56

will actually display as;

05:50:56

2)If option 1 is not going to work in your circumstances, then you can convert back to hh:nn:ss with an expression like;

Format(Int([YourSumField]/3600),"00") & ":" & Format(Int(([YourSumField]-(Int([YourSumField]/3600)*3600))/60),"00") & ":" & Format([YourSumField] Mod 60,"00")

 

[gracegrenier]

Thank you, that helps