How to calculate time difference over 24 hours in Access VBA

I was looking around the internet to find a better way to calculate time difference between two values on my MS Access Table. And finally I found a good solution on how to calculate time difference that works over 24 hours time. See how this works for me:

Table Design

table 1

So basically, I have three main columns. From, to and result which is the start time, end time and the time differences. Now, I can put the following code to calculate the time differences between from and to.

[codesyntax lang=”vbnet”]

Private Sub result_GotFocus()
On Error GoTo ErrCode
Dim StartDate As Date
Dim EndDate As Date

StartDate = TimeValue(Me.from.Value)
EndDate = TimeValue(Me.to.Value)
Me.result.Value = Abs((EndDate - StartDate) - (StartDate > EndDate))
Exit Sub:
ErrCode:
MsgBox Err.Description
End Sub

[/codesyntax]

Result

table 2

Admin

Leave a Reply

Your email address will not be published. Required fields are marked *