×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

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 & "));"
    
    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 = "0" & intHours
    Else
        strHours = intHours
    End If
    
    If intMinutes < 10 Then
        strMinutes = "0" & intMinutes
    Else
        strMinutes = intMinutes
    End If
    
    GetTimeTotal = strHours & ":" & 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.

Jim Lunde
compugeeks@hotmail.com
CompuGEEKS
Custom Application Development

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

(OP)

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 "kenny" 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 "#Name?" 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 = "= [Forms]![FrmDECDACC]![SubFrmDED1].Form![Length]"
    strSQL = "SELECT Length FROM Tracks WHERE(((RecordingID) = " & lngID & "));"
    
    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 = "0" & intHours
    Else
        strHours = intHours
    End If
    
    If intMinutes < 10 Then
        strMinutes = "0" & intMinutes
    Else
        strMinutes = intMinutes
    End If
    
    GetTimeTotal = strHours & ":" & strMinutes
End Function
***Code End***

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close