select querry sum

[boge]

Hi, I have telephone records with duration measure in format hh:mm:ss, I want to get a sum of them, so far I understand that first I have to transform the durations in seconds, then made a sum of them, at the end transform the seconds at the starting format hh:mm:ss. Can anyone help me? Thanks!

 

[Eckerslike]

Hi,

To do this first create a query and do a sum of the column that contains these time's. The result of this will be a number.

If you was to format this number as a date it would be fine if it was less than 1 but if it is greater than one it will return a date as in dd/mm/yy hh:mm:ss, which is not what you want.

To overcome this issue you will need to do the following:

Paste the following into a module:

________________________________________________-

Function freformat(Inthhmmss As Double) As String

Dim Temp As String
Dim day As Integer
Dim Temphr As Integer
Dim Tempmm As Integer
Dim Tempss As Integer
Dim Temphhdd As Integer

day = Inthhmmss 'This gives you the number of days

Temp = Format$(Inthhmmss, "hh:mm:ss") 
'This gives you the number of HH:MM:SS that are not part of a full day

Temphr = Left(Temp, 2) 'Returns number of hours
Tempmm = Mid(Temp, 4, 2) 'Returns number of mins
Tempss = Right(Temp, 2) 'Returns number of secs

Temphhdd = Temphr + (day * 24) 'This calculates total number of hours

'The below formats it so that is looks like hh:mm:ss
freformat = Format$(Temphhdd, "00") & ":" & Format$(Tempmm, "00") & ":" & Format$(Tempss, "00")

End Function

_________________________________________

Using the query that you created earlier that gave you a sum, create a new query and put the following in the field section:

Total_Hours: freformat([Name of the summed field])

This will return the total time in the format hh:mm:ss.

Please let me know if you require any further information.

 

[Eckerslike]

I made a bit of a mistake in the above code, in that when it calculates the day it will round it up to the nearest day if greater than .5.

To resolve this problem, please see amended code.

Function freformat(Inthhmmss As Double) As String

Dim Temp As String
Dim day As Integer
Dim Temphr As Integer
Dim Tempmm As Integer
Dim Tempss As Integer
Dim Temphhdd As Integer

day = Inthhmmss 'This gives you the number of days

Temp = Format$(Inthhmmss, "hh:mm:ss")
'This gives you the number of HH:MM:SS that are not part of a full day

Temphr = Left(Temp, 2) 'Returns number of hours
Tempmm = Mid(Temp, 4, 2) 'Returns number of mins
Tempss = Right(Temp, 2) 'Returns number of secs

If Temphr >= 12 Then day = day - 1 ' If the day was rounded up this will correct it

Temphhdd = Temphr + (day * 24) 'This calculates total number of hours

'The below formats it so that is looks like hh:mm:ss
freformat = Format$(Temphhdd, "00") & ":" & Format$(Tempmm, "00") & ":" & Format$(Tempss, "00")

End Function

The only addition is If Temphr >= 12 Then day = day - 1 , this effectively says that if the hours in the day is greater than midday, it would have rounded up 1 day, so this effectively rounds it back down.