Sticky time question

[swampbilly]

I have a bunch of songs. I have most of my song info imported into Access. I'd like to be able to add the times of the songs.

ie;
3:30 (read 3 minutes and 30 seconds)
2:50
4:01
should equal 10:11

I just can't seem to find what I need to get it to work. The goal is to total up durations of queries and filtered results. This is for an internet radio station, which I'm using to practice for the "real world", and sometimes you have to be able to hit the top of the hour exactly. And truthfully, also to time out CDs and the occasional 90 minute tape.

Suggestions?

 

[Russell Hauf]

What version of Access are you using?

 

[swampbilly]

Access97 at work, but I do have 2000 at home.

 

[Russell Hauf]

Ok, here is something that should work for you. First, you have to paste some code into a regular module (Modules - New, paste in the code). Here are the ADO (Access 2000) and DAO (Access 97) versions, respectively:

ADO:

Public Function SumTimes(strTimeFld As String, strTable As String, _
                         Optional strCriteria As String) As String
                         
    Dim lngMinutes As Long
    Dim lngSeconds As Long
    Dim lngPos As Long
    Dim rst As ADODB.Recordset
    Static strGroup As String
    Dim strSQL As String
    
       
    If Len(strCriteria) = 0 Then
        strSQL = "SELECT * FROM " & strTable
    Else
        If Right(RTrim(strCriteria), 1) = "=" Then
            strCriteria = RTrim(strCriteria)
            strCriteria = Left(strCriteria, Len(strCriteria) - 1) & " is Null"
        End If
            
        strSQL = "SELECT * FROM " & strTable & " WHERE " & strCriteria
    End If
    
    Set rst = New ADODB.Recordset
    rst.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
    
    If rst.EOF Then
        GoTo ExitHere
    End If
    
    With rst
        Do While Not .EOF
            lngPos = InStr(.Fields(strTimeFld), ":")
            If lngPos = 0 Then
                Exit Function
            End If
            If lngPos > 1 Then
                lngMinutes = lngMinutes + CLng(Left(.Fields(strTimeFld), lngPos - 1))
            End If
            lngSeconds = lngSeconds + CLng(Mid(.Fields(strTimeFld), lngPos + 1))
            .MoveNext
        Loop
        .Close
    End With
    
    lngMinutes = lngMinutes + (lngSeconds \ 60)
    lngSeconds = lngSeconds Mod 60
    
    SumTimes = CStr(lngMinutes) & ":" & CStr(lngSeconds)
    
ExitHere:
    Set rst = Nothing
    Exit Function
    
HandleErr:
    Select Case Err.Number
    Case Else
        MsgBox Err.Description, vbCritical, Err.Number
        Resume ExitHere
    End Select
    
End Function

DAO:

Public Function SumTimesDAO(strTimeFld As String, strTable As String, _
                         Optional strCriteria As String) As String
                         
    Dim lngMinutes As Long
    Dim lngSeconds As Long
    Dim lngPos As Long
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
       
    If Len(strCriteria) = 0 Then
        strSQL = "SELECT * FROM " & strTable
    Else
        If Right(RTrim(strCriteria), 1) = "=" Then
            strCriteria = RTrim(strCriteria)
            strCriteria = Left(strCriteria, Len(strCriteria) - 1) & " is Null"
        End If
            
        strSQL = "SELECT * FROM " & strTable & " WHERE " & strCriteria
    End If
    
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
    
    If rst.EOF Then
        GoTo ExitHere
    End If
    
    With rst
        Do While Not .EOF
            lngPos = InStr(.Fields(strTimeFld), ":")
            If lngPos = 0 Then
                Exit Function
            End If
            If lngPos > 1 Then
                lngMinutes = lngMinutes + CLng(Left(.Fields(strTimeFld), lngPos - 1))
            End If
            lngSeconds = lngSeconds + CLng(Mid(.Fields(strTimeFld), lngPos + 1))
            .MoveNext
        Loop
        .Close
    End With
    
    lngMinutes = lngMinutes + (lngSeconds \ 60)
    lngSeconds = lngSeconds Mod 60
    
    SumTimesDAO = CStr(lngMinutes) & ":" & CStr(lngSeconds)
    
ExitHere:
    Set rst = Nothing
    Exit Function
    
HandleErr:
    Select Case Err.Number
    Case Else
        MsgBox Err.Description, vbCritical, Err.Number
        Resume ExitHere
    End Select
    
End Function

These functions are used like the database functions (DSUM, etc.). And in your case this field will always need to be used in a GROUP BY, or Totals query. And the field will need to have GROUP BY selected for it since it is really a string (and I'm assuming that your times are stored as text - is this correct?). So you would call it something like this:

TotalTime = SumTimesDAO("[fldSongTime]","[tblMusic]")

or, if you want to group by another field, say Artist - you'd select Artist, then in the next query field put something like:

TotalTime = SumTimesDAO("[fldSongTime]","[tblMusic]","[fldArtist] = '" & [fldArtist] & "'")

If you need more help, let me know.

-Russell