Add Time

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Using Access 2002 for a music database, there is 1 field for track length.
Its format is Short Time.

I would like to total track length time for the recording, which I thought I
could achieve using the DSum() function but that does not work

Is there another function I can use or what is the best way to get the total
time

tia
Tom
 
Probably best to use a Number field to store a duration in seconds or
whatever is the finest granularity you need. Easy enough to display it as
time, and you can use some unbound controls to it can be entered in h, m,
and s, and then converted to be stored in seconds.

More info in:
Calculating elapsed time
at:
http://members.iinet.net.au/~allenbrowne/casu-13.html
 
Tom said:
Using Access 2002 for a music database, there is 1 field for track
length. Its format is Short Time.

I would like to total track length time for the recording, which I
thought I could achieve using the DSum() function but that does not
work

Is there another function I can use or what is the best way to get
the total time
Click to expand...

Don't use a date/time field to store duration (such as track length).
That's not what the date/time data type is for; it's for storing points
in time, not lengths of time. Instead, define your TrackLength field as
a Number/Long Integer field, and define it as the number of seconds the
track lasts. To display the number of seconds as a time, use a function
like TimeToSeconds(), posted below. To convert a time entered in
"hh:mm:ss" format into a simple number of seconds, use a function like
SecondsToTime(), posted below.

When your track lengths are stored in seconds, rather than as date/time
values, you'll be able to use normal summing functions to add them up.


'------ start of code -----
Function SecondsToTime(ByVal lngSeconds As Long) As String

Dim strHours As String
Dim lngHours As Long
Dim strMinutes As String
Dim lngMinutes As Long
Dim dblSeconds As Double
Dim strMinFmt As String
Dim strSecFmt As String

lngHours = lngSeconds \ 3600
lngSeconds = lngSeconds Mod 3600
lngMinutes = lngSeconds \ 60
lngSeconds = lngSeconds Mod 60

If lngHours > 0 Then
strHours = lngHours & ":"
strMinFmt = "00"
Else
strMinFmt = "0"
End If

If lngMinutes > 0 Then
strMinutes = Format(lngMinutes, strMinFmt) & ":"
strSecFmt = "00"
Else
strSecFmt = "0"
End If

SecondsToTime = _
strHours & strMinutes & Format(lngSeconds, strSecFmt)

End Function


Function TimeToSeconds(strTime As String) As Long

Dim strParts() As String
Dim lngHoursToSecs As Long
Dim lngMinutesToSecs As Long
Dim lngSeconds As Long

strParts = Split(strTime, ":")

Select Case UBound(strParts)
Case 0
lngSeconds = Val(strParts(0))
Case 1
lngMinutesToSecs = Val(strParts(0)) * 60
lngSeconds = Val(strParts(1))
Case 2
lngHoursToSecs = Val(strParts(0)) * 3600
lngMinutesToSecs = Val(strParts(1)) * 60
lngSeconds = Val(strParts(2))
Case Else
Err.Raise 5, "TimeToSeconds", _
"Invalid argument '" & strTime & "'."
End Select

TimeToSeconds = _
lngHoursToSecs + lngMinutesToSecs + lngSeconds

End Function
'------ end of code -----
 
Back
Top