Dec 11 2021 05:40 PM
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.