Does Access support a "Time" format (not "Time Of Day")?

[joemagiera]

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.

[#2]

@joemagiera 

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.

[#3]

@joemagiera 

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.