Minutes and Seconds

[downunder]

Does anyone know if there is a function for a query to add up minutes and seconds. My database contains records of music album tracks with duration for each track. I just want to add up the minutes and seconds of a selected number of tracks.

 

[WayneRyan]

downunder,

I don't know what format your data is in, or what format you
want your data in, but ...

Minutes = TotalMinutes + Int(TotalSeconds/60)
Seconds = Mod(TotalSeconds, 60)

Wayne

 

[downunder]

Thanks for the reply, Wayne. I actually had a single time field based on the format 'min:sec', e.g. 10:15 (10 min, 15 secs) and wanted to just sum up the field in the query.

With your solution, it looks like I need 2 fields, one for minutes and another for seconds and then calculate them separately. This I will do if it is not possible to find a method of summing up the one field.

 

[namliam]

you can "simply" sum/add date/time fields as you would any "normal" number field.....

10:15 + 5:15 + 15:30+ 30:00 = 1:00:00

Regards

 

[Mile-O]

you can "simply" sum/add date/time fields as you would any "normal" number field.....

10:15 + 5:15 + 15:30+ 30:00 = 1:00:00

If you only have times then summing them works on a 24 hours clock - you need to then convert the number of days into hours, too.

 

[downunder]

Yeah Namliam, that's what I thought too but it's not happening. To make it simple I only set up 2 entries to test - 7:29 and 4:42 which when added should be 12:11. Trouble is, it's returning a strange figure, 0.5076, when I sum the 2 records.

The field's data type is date/time and the format is short time. Maybe this is incorrect?

 

[namliam]

If you only have times then summing them works on a 24 hours clock - you need to then convert the number of days into hours, too.

Hmz, i started to post that would be a format thing. but looking again it seems it not posible to have 25:00:00. Where i seem to remember doing that somewhere ... something like Format(,"hhh:nn:ss") but that dont seem to work

Regards

 

[Mile-O]

Hmz, i started to post that would be a format thing. but looking again it seems it not posible to have 25:00:00. Where i seem to remember doing that somewhere ... something like Format(,"hhh:nn:ss") but that dont seem to work

Regards

You probably done it in Excel.

I have a function, however, that will sum time values in a query and return a string value (that looks like a time value) such as "78:23:08".

Function TimeConversion(ByVal dteTime As Date) As String

    On Error GoTo TimeConversion

    Dim lngDays As Long, lngHours As Long, lngMinutes As Long, lngSeconds As Long
    Dim intCounter As Integer, strTemp As String
    
    ' using the 'Csng' function, convert the given time to broken down values
    lngDays = Int(CSng(dteTime))
    lngDays = lngDays * 24 ' turn number of days to hours
    lngHours = Int(CSng(dteTime * 24))
    lngMinutes = Int(CSng(dteTime * 1440))
    lngSeconds = Int(CSng(dteTime * 86400))
    lngHours = lngDays + (lngHours Mod 24) ' calculate total of hours
    lngMinutes = lngMinutes Mod 60 ' get actual minutes
    lngSeconds = lngSeconds Mod 60 ' get actual seconds
    
    ' fix single figure values for minutes, i.e change :5 to :05
    Select Case lngMinutes
        Case Is = 0
            strTemp = Str(lngHours) & ":00"
        Case Is < 10
            strTemp = Str(lngHours) & ":0" & lngMinutes
        Case Else
            strTemp = Str(lngHours) & ":" & Str(lngMinutes)
    End Select
    
    ' fix single figure values for seconds, i.e change :5 to :05
    Select Case lngSeconds
        Case Is = 0
            strTemp = strTemp & ":00"
        Case Is < 10
            strTemp = strTemp & ":0" & Str(lngSeconds)
        Case Else
            strTemp = strTemp & ":" & Str(lngSeconds)
    End Select
    
    ' the 'Str()' function may append spaces to the newly formed string, this loop eliminates these
    For intCounter = 1 To Len(strTemp)
        If Mid(strTemp, intCounter, 1) = " " Then
            ' do nothing
        Else
            TimeConversion = TimeConversion & Mid(strTemp, intCounter, 1)
        End If
    Next intCounter
    
Exit_TimeConversion:
    Exit Function

Err_TimeConversion:
    MsgBox Err.Number & Err.Description
    Resume Exit_TimeConversion
    
End Function

Could probably be cut down but I wrote it a while back and have never looked at it since.

 

[downunder]

Thanks Mile-O-Phile, I'll have a go at that although I was hoping that it was not necessary to write VB code for such a simple thing. It seems to me that Access should have a function to sum such a field from the Query grid. Maybe not!

 

[Mile-O]

In the query grid, this works:

It's based on another query where I summed a range of times.

Actual: (Int([SumOfTimeField])*24)+Format(Hour([SumOfTimeField]),"00") & ":" & Format(Minute([SumOfTimeField]),"00")

 

[raskew]

Think you may be off on a tangent. The problem is with elapsed minutes and seconds, not times. #7:29# to Access represents 7:29 A.M. (hours and minutes). It's stored as a portion of day. Try the following in the debug window to see why you're coming up with some strange results:

x = #7:29#
? x
7:29:00 AM
? cdbl(x)
0.311805555555556
y = #4:42#
? y
4:42:00 AM
? cdbl
0.195833333333333
? x + y
12:11:00 PM
? cdbl(x + y)
0.507638888888889

We're looking for 12 minutes and 11 seconds, but are getting what equates to 731 minutes.

Back to the drawing board!

 

[Fornatian]

Given that raskews proposals are right and your database thinks you're talking hours and minutes not minutes and seconds I've used a TimeValue function to create the time, see the following SQL.([TheTime] = YourField

SELECT Sum(TimeValue("0:" & CStr(Mid([TheTime],1,2)) & ":" & CStr(Mid([TheTime],4,2)))) AS MinutesAndSeconds
FROM Table1;

if that works for you it can be translated into a VBA function to make life easier by reducing it to one simple function call.

 

[downunder]

Thanks both to Raskew and Fornation. Yes, Raskew is correct, Access interprets the entries as time of day so I will try Fornation's solution.

Books don't help much but this forum sure does!

 

[Fornatian]

Seeing as there are 60 seconds in a minute and 60 minutes in an hour it seems that dividing the source by 60 will leave you with the conversion complete.

TheTime: [YourTimeField]/60

 

[raskew]

Mileo-

You're strangely silent here. Sure you don't want to jump back in and increment your total number of posts? Maybe 'oops' would be appropriate.

Best wishes,

Bob

 

[downunder]

Sorry Fornation, couldn't get the SQL query or dividing source by 60 to work. Will keep trying however.

 

[Mile-O]

You're strangely silent here.

It's the weekend; things to do.

Sure you don't want to jump back in and increment your total number of posts? Maybe 'oops' would be appropriate.

You know, you're absolutely right.

Oops!

 

[Fornatian]

Let's have a sample to play with (Access 97 please for the poor luddites )

 

[downunder]

The simplest way it to set up a table with just one field, call it 'duration' or anything you like, make the date type 'date/time' and the format 'short time'.

Make only 2 entries into the table in this format e.g. 5:52 (5 minutes and 52 seconds) and 3:26. Open a query and try summing up the 2 fields in the query. The answer, of course, is 9:18 but I can find no way of doing this simply.

 

[raskew]

Mileo-

Gotta give you credit. You're the ultimate diplomat! I would have responded far uglier!

Best wishes,

Bob