Running Sum on times rolls over at 24 hours 1

[thompc]

If I add times in records using Running Sum the time rolls over when the total exceeds 24 hours e.g. when the next record time of say 1:30 is added to the previous total of 23:00 I get 00:30 instead of 24:30 displayed. How do I stop this happening?

 

[scriverb]

You have to convert your time value to a decimal equivalent of hours.minutes 3:30 = 3.5 hours After you perform your running some of the times then you must convert it back to a days hours minutes format through code.

This should get your thinking on the right track. I answered a similar question like this recently with some code to perform this function. I will see if I can find the thread and post it here.

Bob Scriver

 

[scriverb]

Couldn't find the Q/A but here is a little exercise to demo how to handle this problem and it can be adapted to your needs. Let’s create a table called tblTimes with a field called TimeField which is a Date/Time field. Put some times in the records to be added up. These would be like your running sum records. They should be like 1:30 , 5:24, 10:46, etc.

Create a module with the following:
Dim vHours As Double
Dim vMinutes As Double

Function TimeConv(vTime)
vHours = DatePart("h", vTime)
vMinutes = DatePart("n", vTime) / 60
TimeConv = vHours + vMinutes
End Function

Create a new form just for this exercise and place a text box on the form to view the results. Call it TimeResults.

Then put the following code in the OnClick event procedure of a button:
Dim MyDB As Database
Dim MyRS As Recordset
Dim vTimeRollup As Double
Dim vHours As Integer
Dim vMinutes As Integer
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblTimes", dbOpenDynaset)
MyRS.MoveFirst
Do
vTimeRollup = vTimeRollup + TimeConv(MyRS("TimeField&quot)
MyRS.MoveNext
Loop Until MyRS.EOF
vHours = Int(vTimeRollup)
vMinutes = (vTimeRollup - vHours) * 60
If vMinutes >= 60 Then
vHours = vHours + CInt(vMinutes / 60)
vMinutes = vMinutes - (CInt(vMinutes / 60) * 60)
End If
Me![TimeResults] = vHours & " hrs. " & vMinutes & "min."

Click the button to rollup the results and display the added time values in the text box on the form.
I have displayed them in a text string as you can't and should't try to store them in a date/time field.
You can however display them in a string representation just by cancatenating the values together as
in the following:

Me![TimeResults] = Format(vHours, "00&quot & ":" & Format(vMinutes, "00&quot

See if this concept can be used in your application.

Bob Scriver

 

[thompc]

Thanks for the tip. I wrote two conversion functions, one to convert to "decimal time", then do the running sum, and another to convert back to "true time" and used these on every record line in the report.

 

[scriverb]

Great. I am glad that I could help get you on the right track. This question comes up a lot and I have only been able to do this performing decimal mathematics on the time representation to achieve acceptable results.

Bob Scriver

 

[scriverb]

thompc: I ran into a link that could also be useful if only to reiterate some of the concepts of your questions and final conclusion. This site talks about the conversion of date and time decimal fields and the calculations that can be made.

Thought you might be interested.

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

Bob Scriver