Adding times that exceed 24 hours?

[Thiazi]

I know this has been discussed quite a bit, but I can't seem to get my
head around the solution to this problem. I have a table with times
stored in 24 hour format and need to add those times on an access
report. Currently, I'm using sum and the column name; however, when
the time rolls over past 24 hours, a day is added (so 1:20:23:20 would
be 44 hours, 23 minutes, 20 seconds). Instead of 1:20:23:20, I need
44:23:20 to show on the report itself.

I understand Access isn't setup to be able to natively handle such a
task. I'm also not finding the difference between two times - I just
want to add all times that appear in a column for a specified query
and produce the result in hh:nn:ss format no matter how many hours it
may be.

What would be the easiest way to accomplish this?

 

[Joan Wild]

http://www.mvps.org/access/datetime/date0009.htm

 

[Scottgem (MVP)]

The ONLY way to accomplish this is to display the result as a string.
The following function will take the sum of time values and convert to
total hours and minutes:

Public Function TotalTime(dblTimeDif) As String

Dim intDays As Integer
Dim dblHrs As Double
Dim intHours As Integer
Dim dblMinutes As Double
Dim intMinutes As Integer
Dim intTime As Integer
Dim intFraction As Integer


intDays = Int(dblTimeDif)
dblHrs = (dblTimeDif - intDays) * 24
intHours = Int(dblHrs)
dblMinutes = dblHrs - intHours
intHours = intHours + (intDays * 24)
intMinutes = Int(dblMinutes * 60)


TotalTime = Str(intHours) & ":" & Trim(Str(intMinutes))

End Function

HTH
Scott<>
Microsoft Access MVP 2007

 

[Thiazi]

Where would I add this function (I assume as a module) and reference
it on the reports? Right now I just have a textbox with control set to
=sum([field])

 

[Thiazi]

Where would I add this function (I assume in a module) and how would I
call it from the report to convert the result? Right now I have a
textbox in the report using =sum([field])

 

[i_takeuti]

I know this has been discussed quite a bit, but I can't seem to get my
head around the solution to this problem. I have a table with times
stored in 24 hour format and need to add those times on an access
report. Currently, I'm using sum and the column name; however, when
the time rolls over past 24 hours, a day is added (so 1:20:23:20 would
be 44 hours, 23 minutes, 20 seconds). Instead of 1:20:23:20, I need
44:23:20 to show on the report itself.

I understand Access isn't setup to be able to natively handle such a
task. I'm also not finding the difference between two times - I just
want to add all times that appear in a column for a specified query
and produce the result in hh:nn:ss format no matter how many hours it
may be.

What would be the easiest way to accomplish this?

 

[Thiazi]

I've successfully implemented this. However - can you only call it
once in a report? If I attempt to call it on more than one field at a
time, I get #Errors and an automation error 440.