VBA – Access – convert “HH:MM:SS” to double and back

MS Access is something very … interesting. For the last week I was building something on it, which includes dealing with data in HH:MM:SS format. Access has a pretty good feature for dealing with this, but this feature somehow crashes after the 24th hour… There are plenty of ways to go around this restriction and probably everyone has its own favourite method. So here is mine – I have created two functions strDblToString and dblStringToDbl , which work quite OK. They read a string in the format HH:MM:SS and calculate its seconds. Then, once you have the seconds as double you may do any mathematical operation you want. With strDblToString you calculate back the double to a string, which can be displayed in Access. Quite slow, but if you do not mind 0.02 seconds delay it is your thing! 🙂

Here comes the code:

Public Function dblStringToDbl(sDate As String) As Long

    Dim hours           As String
    Dim minutes         As String
    Dim seconds         As String

    hours = Trim(Split(sDate, ":")(0))
    minutes = Trim(Split(sDate, ":")(1))
    seconds = Trim(Split(sDate, ":")(2))

    dblStringToDbl = CLng(hours) * 3600 + CLng(minutes) * 60 + CLng(seconds)

End Function

Public Function strDblToString(dblSeconds As Double) As String

    Dim hours           As Long
    Dim minutes         As Long
    Dim seconds         As Long
    Dim dUsedSeconds    As Long

    Dim sHours          As String
    Dim sMinutes        As String
    Dim sSeconds        As String

    dUsedSeconds = CLng(dblSeconds)

    hours = Int(dUsedSeconds / 3600)
    minutes = Int((dUsedSeconds Mod 3600) / 60)
    seconds = Int((dUsedSeconds Mod 3600) Mod 60)

    sHours = Format(hours, "00")
    sMinutes = Format(minutes, "00")
    sSeconds = Format(seconds, "00")

    strDblToString = sHours & ":" & sMinutes & ":" & sSeconds

End Function

Enjoy it!