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]

 

[jimmythegeek]

Put a text box on your main form.
Call it txtTimeTotal
In the control source of txtTimeTotal put this:
=GetTimeTotal(Me!ID)
Change the Me!ID to reflect whatever the name of your ID field is on the mainform
Now copy and paste everything between the lines below, into a new module, changing the red text to match your fields and tables.

===============
Function GetTimeTotal(lngID) As String
Dim db As Database
Dim rs As Recordset
Dim strSQL As String, strHours As String, strMinutes As String
Dim intLeft As Integer, intRight As Integer, intValue As Integer, intTotal As Integer, intHours As Integer, intMinutes

strSQL = "SELECT YourTimeField FROM YourSubformTableName WHERE(((id) = " & lngID & &quot);"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
intLeft = Left(!test, 2)
intRight = Right(!test, 2)
intLeft = intLeft * 60
intValue = intLeft + intRight
intTotal = intTotal + intValue
.MoveNext
Loop
End If
.CLOSE
End With

intHours = intTotal / 60
intMinutes = intTotal Mod 60

If intHours < 10 Then
strHours = &quot;0&quot; & intHours
Else
strHours = intHours
End If

If intMinutes < 10 Then
strMinutes = &quot;0&quot; & intMinutes
Else
strMinutes = intMinutes
End If

GetTimeTotal = strHours & &quot;:&quot; & strMinutes
End Function
===============

Save the module as anything you want.
If all of the names were changed correctly, you should be able to open the form and the txtTimeTotal text box should calculate the time for that particular id. [sig]<p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development[/sig]

 

[kgreen]

Hi Jimmy,

Many thanks for the reply and taking the time to help with the above code. I am sorry that I have not replied until now but I have only just had a chance to try it due to work commitments.

I am sorry to say that the dreaded &quot;kenny&quot; factor has kicked in (what ever I try I end up breaking) and I cant get it to work. Forgive me if I go into detail, but Im sure you need all the facts.

Firstly. I created the text box on my main form exactly as you said to but when I look at it, the box displays &quot;#Name?&quot; so it would appear that it cant find its control source.

secondly, I cut and pasted your code into a new Modual and change the Ctl references as you said to to reflect the correct information. So where am I going wrong ?

I have placed the exact code from my modual at the end of this post, in case I have changed something that I shouldn't have by mastake.

Just out of interest, am I correct in assuming that the modual queries the underlieing table for its data based around the uniqueID I give to each Record Group, If this is so then the details I have entered are correct.

For example.
The table containing the data Is called [TblTracks]. The Ctl/field that contains the Length data is called [Length] (short time format) and the UniqueID ctl/field is called [RecordingID].

And finally, in case it counts, the form that I have placed the [txtTimeTotal] on is called [FrmDVCDAC] and I have named the modual [basGetTotalTime].
Also, when I type the line =GetTimeTotal(Me!RecordID) into the text boxes Contol Source, access changes it to: =GetTimeTotal([Me]![RecordingID]).


Any further suggestions or advice would be gratefully accepted.

***Code Start***
Function GetTimeTotal(lngID) As String
Dim db As Database
Dim rs As Recordset
Dim strSQL As String, strHours As String, strMinutes As String
Dim intLeft As Integer, intRight As Integer, intValue As Integer, intTotal As Integer, intHours As Integer, intMinutes
'[Forms]![FrmDECDACC]![SubFrmDED1].Form![RecordingID].ControlSource = &quot;= [Forms]![FrmDECDACC]![SubFrmDED1].Form![Length]&quot;
strSQL = &quot;SELECT Length FROM Tracks WHERE(((RecordingID) = &quot; & lngID & &quot);&quot;

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
intLeft = Left(!test, 2)
intRight = Right(!test, 2)
intLeft = intLeft * 60
intValue = intLeft + intRight
intTotal = intTotal + intValue
.MoveNext
Loop
End If
.Close
End With

intHours = intTotal / 60
intMinutes = intTotal Mod 60

If intHours < 10 Then
strHours = &quot;0&quot; & intHours
Else
strHours = intHours
End If

If intMinutes < 10 Then
strMinutes = &quot;0&quot; & intMinutes
Else
strMinutes = intMinutes
End If

GetTimeTotal = strHours & &quot;:&quot; & strMinutes
End Function
***Code End*** [sig][/sig]