Sum Time Greater Than 24 Hours

[abbaddon223]

Hi,

I have a field in my report [Idle]

This has a time in a HH:MM:SS format (so 01:38:23)

What I need to be able to do is sum this greater than 24 hours and keep the format of HH:MM:SS - ideally I need to do this in the form field that I'll be using to sum it.

Any hel is greatly appreciated!!!!

Phil.

 

[billmeye]

I'm guessing your trying to store your time as a data type Date. Access stores any data type date as date:time (01/01/2000 12:00:00 PM) and all you can do is format to show only what part you choose, i.e, Time only. Then, when you try to do math Access is performing the math as an interval over the date period, not adding values. If you want to store just hours, minutes, seconds then store it as a number of just seconds and you need to do the formatting to make it look like hh:mm:ss. But, if you already have a lot of data stored as date data type, here is a function to do the conversion so you can add the values, it results in a string as there is no data type to store it as hh:mm:ss.

Function AddTimes(GetTime1, GetTime2 As Date) As String
Dim GetSec1, GetSec2 As Double
GetSec1 = Format(GetTime1, "h") * 3600 + Format(GetTime1, "n") * 60 + Format(GetTime1, "s")
GetSec2 = Format(GetTime2, "h") * 3600 + Format(GetTime2, "n") * 60 + Format(GetTime2, "s")
If GetSec1 + GetSec2 < 3600 Then AddTimes = "0:" Else AddTimes =  Int((GetSec1 + GetSec2) / 3600) & ":"
AddTimes = AddTimes & Format(Int(((GetSec1 + GetSec2) Mod 3600) / 60), "00") & ":" & Format(Int((GetSec1 + GetSec2) Mod 60), "00")
End Function