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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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!