Adding up a range of values in "Time Format" - help needed

[kgreen]

Is there a function/query that will add up a range of cells that contain time values, for example: 00:30 + 00:30 = 01:00
and then display the results in a txt field on a from.

I have a subform called frmtimes and a txt field called txttime. The subform displays records based on a table and the number of records can vary depending on what set the user selects. each set of records has a unique ID that refers to the set as a whole so identifying the complete set is not a problem.

Any hints or suggestions would be very helpful.

Thanks in advance

Kenny [sig][/sig]

 

[DougP]

here is a function I wrote to subtract two times.
So you could follow the same procedure to add them.
Basically you have to break apart the time into it's components.

Public Function DiffTime(TimeIN, TimeOUT)
Dim msg As String
Dim Hr1, Mn1, SS1, Hr2, Mn2, SS2, Mint As Variant
Dim DiffTime2
On Error GoTo Errhand

If IsNull(TimeIN) Then
msg = "You did not punch IN yet" & Chr$(10)
MsgBox msg & "Please do so"
GoTo ExitMe:
End If
'debug.print "Time IN "; TimeIN, "TimeOut "; TimeOUT
Hr1 = DatePart("h", TimeIN)
Mn1 = DatePart("n", TimeIN)
SS1 = DatePart("s", TimeIN)
Hr2 = DatePart("h", TimeOUT)
Mn2 = DatePart("n", TimeOUT)
SS2 = DatePart("s", TimeOUT)

DiffTime2 = TimeSerial(Hr2 - Hr1, Mn2 - Mn1, SS2 - SS1)
'debug.print "DiffTime2 "; DiffTime2
If DiffTime2 < &quot;1:00 AM&quot; Then
'debug.print DiffTime2
Mint = DatePart(&quot;n&quot;, DiffTime2)
If Len(Mint) = 1 Then Mint = &quot;0&quot; & Mint
DiffTime2 = &quot;00:&quot; & Mint & &quot;:&quot; & DatePart(&quot;s&quot;, DiffTime2)
DiffTime = DiffTime2
Else
DiffTime = Format(DiffTime2, &quot;HH:MM:SS&quot
End If
'debug.print &quot;DiffTime > &quot;; DiffTime

ExitMe:
Exit Function

Errhand:
Select Case Err.Number
Case 94
' Invalid use of null
MsgBox &quot;You did not punch in&quot;
Case Else
MsgBox Err.Number & &quot; &quot; & Err.Description
Resume ExitMe
End Select
End Function [sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]

 

[kgreen]

Hi Doug,

Many thanks for your reply, I am grateful for all the help you guys give in here but looking at your code gave me a right headache (heh heh), very complicated stuff.

I think the problem is I did not explaine my needs very well. I may well be wrong but I think your code adds up time from different txt boxes - am I correct?

The values I need to add together are all in the SAME txt box but are just different for each record. Here is an example.

RecID(auto No) Name (txt) Time (short time format MM:SS)
1 Joe Bloggs 03:30
2 John Doe 02:30

Total Time 06:00

I tried using =Sum([Time])as the record source for the text box in which i want to display the total and it worked fine as long as I have the control format of the box in the underlieing table set to &quot;number - Double&quot; but I need it to be set to &quot;Date/Time - short format&quot;. I do have a very good reason for this so any further ideas would be most appreciated.

Regards

Kenny [sig][/sig]

 

[THoey]

I wrote a really dirty function that does this. It receives the RecID that you pass it and adds up the total Hours and Minutes. The only problem that I had to adjust for is that you say that the RecID is an Autonumber field. That means that each record will have a unique number. I changed that to a number field so that I could grab groups of records.

Like I said, this is dirty and needs cleaning up. There probably is also a better way to do it.

Public Function TimeAdd(ID)
Dim db As Database
Dim rst As Recordset
Dim TimeIN As Date
Dim Hr As Integer
Dim Mn As Integer
Dim TotHr As Integer
Dim TotMn As Integer
Dim TotTime As Integer
Dim TimeOut As String
Dim result As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;select * from Hours where Hours.RecID = &quot; & Str(ID))

rst.MoveFirst

' Accumulate total Minutes
Do While Not rst.EOF
TimeIN = rst!Time
Hr = DatePart(&quot;h&quot;, TimeIN)
Mn = DatePart(&quot;n&quot;, TimeIN)
TotHr = TotHr + Hr
TotMn = TotMn + Mn
rst.MoveNext
Loop

TotTime = (TotHr * 60) + TotMn

TotHr = TotTime \ 60
TotMn = TotTime Mod 60

' Convert total minutes to HH:MM format
If TotHr < 10 Then
TimeOut = &quot;0&quot; & Trim(Str(TotHr)) & &quot;:&quot;
Else
TimeOut = Str(TotHr) & &quot;:&quot;
End If
If TotMn < 10 Then
TimeOut = TimeOut & &quot;0&quot; & Trim(Str(TotMn))
Else
TimeOut = Time & Str(TotMn)
End If

result = MsgBox(&quot;The total is &quot; & TimeOut, vbOKOnly)

End Function

Hope this helps get you going... [sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]