Dec 11 2021 05:40 PM
Does Access support a Time format, as in MM:SS.TT, as in, for example, 15:10.42, meaning 15 minutes, 10.42 seconds)? Or am I relegated to this being a text field?
If you're keeping track and doing stats of timed athletic performance, swimming and running being prime examples, it would sure be nice to be able to do mathematical functions using this as a format.
Dec 12 2021 10:27 AM - edited Dec 12 2021 10:41 AM
Let's be clear about what you want. You correctly call this out as 'not "Time of Day"'. And you also correctly identify it as a "Time" format, i.e. not a time value.
What you want is an elapsed time, which is a number of minutes, or number of seconds, etc., FORMATTED in such as way as to resemble a standard time. So we can apply formatting to many values, numbers as well as strings, to DISPLAY them in whatever format we need.
So, the answer is yes, to display this as an elapsed time, format a text string that way. It's not a time of day anyway.
That said, if you want to do math calculations on dates and times, you have to use the actual dates and times. The DateDiff() formula will do all this for you.
The way to do it is to calculate the total number of Seconds, or tenths of seconds between the two dates and then further calculate the hours, minutes, seconds, and tenths needed from that total elapsed time. Then concatenate those pieces as you want.
Dec 14 2021 04:53 AM
Access does not support this by default. You will need custom coding for this.
To read and convert such a string to a date/time value including the hundredths of seconds:
' Converts a string expression for a sport result with
' 1/100 of seconds or 1/1000 of seconds to a date value
' including milliseconds.
'
' Example:
' "3:12:23.48" -> 03:12:23.480
' "20:06.80" -> 00:20:06.800
' "19.56" -> 00:00:19.560
' "49.120" -> 00:00:49.120
' "23.328" -> 00:00:23.328
'
' 2018-01-24. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function CDateRaceTime( _
ByVal RaceTime As String) _
As Date
Dim Values As Variant
Dim Hour As Integer
Dim Minute As Integer
Dim Second As Integer
Dim Millisecond As Integer
Dim Result As Date
Values = Split(RaceTime, MillisecondSeparator)
Select Case UBound(Values)
Case 0
' No split seconds.
Case 1
Millisecond = Val(Left(Values(1) & "00", 3))
Case Else
' Invalid expression.
End Select
If UBound(Values) <= 1 Then
' Split time part.
Values = Split(Values(0), TimeSeparator)
Select Case UBound(Values)
Case 0
Second = Val(Values(0))
Case 1
Second = Val(Values(1))
Minute = Val(Values(0))
Case 2
Second = Val(Values(2))
Minute = Val(Values(1))
Hour = Val(Values(0))
Case Else
' Invalid expression.
Millisecond = 0
End Select
Result = TimeSerialMsec(Hour, Minute, Second, Millisecond)
End If
CDateRaceTime = Result
End Function
and to format those values for display:
' Formats a time duration rounded to 1/100 second with trailing zeroes
' and with no leading hours and minutes if these are zero.
' This format is typical for sports results.
'
' Examples:
' 1:02:07.803 -> 1:02:07.80
' 02:07.803 -> 2:07.80
' 14.216 -> 14.22
'
' 2016-12-11. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function FormatRaceTime( _
ByVal Expression As Variant) _
As String
Const FormatHour As String = "h:n"
Const FormatMinute As String = "n:s"
Const FormatBase As String = "s.ff"
Dim Duration As Date
Dim Format As String
Dim Result As String
If IsDateMsec(Expression) Then
Duration = CDateMsec(Expression)
If Hour(Duration) > 0 Then
Format = FormatHour & FormatMinute
ElseIf Minute(Duration) > 0 Then
Format = FormatMinute
End If
Format = Format & FormatBase
Result = FormatExt(Expression, Format)
End If
FormatRaceTime = Result
End Function
Both functions use several supporting functions - too much to post here.
For the full code, look up my project VBA.Date.
The two functions above are taken from module DateText.