Adding times >24 hours using TotalTime

[thiazi]

Hi all,

I'm trying to add times on a report and make the sum appear in hh:mm:ss format regardless of how many hours have elapsed. The way Access is handling this by default is rolling over to d:hh:mm:ss when 24 hours is reached.

I have used the following code:

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 dblSeconds As Double
Dim intSeconds As Integer
Dim intTime As Integer
Dim intFraction As Integer
Dim strResult As String


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

intMinutes = Int(dblMinutes)
dblSeconds = (dblMinutes - intMinutes) * 60
intSeconds = Int(dblSeconds)


strResult = Str(intHours) & ":"
If intMinutes < 10 Then
  strResult = strResult & "0" & Trim(Str(intMinutes))
Else
  strResult = strResult & Trim(Str(intMinutes))
End If
strResult = strResult & ":"
If intSeconds < 10 Then
  strResult = strResult & "0" & Trim(Str(intSeconds))
Else
  strResult = strResult & Trim(Str(intSeconds))
End If
TotalTime = strResult


End Function

This is reformatting the sum exactly how I'd like and is working perfectly for one field (column of time values) in a report. However, when I call the function for the second field (another column of time values), I get an automation error 440 when trying to run the report and get #Error for all of those fields. I'm adding =TotalTime([field]) to the control source to call the function. Am I missing something?

 

[thiazi]

Is it because I'm calling this as a string? It seems to work with just one field only - any other fields give #Error, and the formatting for the fields in the table layout is the same.

 

[The_Doc_Man]

I would not do it QUITE this way. If you wanted hhh...hhh:mm:ss format, you are right to think about "rolling your own" as we sometimes say on the gaming tables.

In essence I would track hours as a SINGLE or DOUBLE, add times in that mode, and only convert on input or output.

The problem with the output format specifications is that Access cannot believe you are dealing with time in that format, so it converts you. ([MENTAL IMAGE]Bill Gates patting you on the head condescendingly while saying, "Don't worry, what we do is for your own good.[/MENTAL IMAGE])

The way around this is to invest a small amout of time in writing a couple of public functions that convert hh:mm:ss into fractional hours and that convert hours with whole and fractional parts into hhh.hhhh:mm:ss, then use the public function where needed to do any conversions.

 

[thiazi]

Thank you for your detailed response. Could you point me in the right direction for such a function? I'm a bit new to Access.

Not sure if this matters, but I'm importing this information from a flat file and it's coming in on the import in hh:mm:ss.

I would not do it QUITE this way. If you wanted hhh...hhh:mm:ss format, you are right to think about "rolling your own" as we sometimes say on the gaming tables.

In essence I would track hours as a SINGLE or DOUBLE, add times in that mode, and only convert on input or output.

The problem with the output format specifications is that Access cannot believe you are dealing with time in that format, so it converts you. ([MENTAL IMAGE]Bill Gates patting you on the head condescendingly while saying, "Don't worry, what we do is for your own good.[/MENTAL IMAGE])

The way around this is to invest a small amout of time in writing a couple of public functions that convert hh:mm:ss into fractional hours and that convert hours with whole and fractional parts into hhh.hhhh:mm:ss, then use the public function where needed to do any conversions.

 

[The_Doc_Man]

Several of the other frequent contributors have posted sample code that relates to time conversion software. Search the forum and look at some of the sample databases.

 

[stopher]

Take a look here.
Stopher

 

[thiazi]

Got it working! Thank you both!