Maybe someone can come up with a formula that you can enter into the
Expression Builder, but for me it's much too complex.
However, here's a function you can put in a module and then call from
wherever you want. It takes two variables as input, StartDateTime and
EndDateTime. These are date fields that include both the date and time (I
use short date format but I don't think it matters). It's output is a string
giving the interval in hours, minutes and seconds in hh:mm:ss format (e.g.
1:30:25).
Here's an example of using the function to populate a text box control
called txtInterval with the inputs being two text box controls called
txtStart and txtFinish:
Me.txtInterval = CalcInterval(Me.txtStart, Me.txtFinish)
Hope this helps...
Regards, Chris
'********* Start Code *************
Public Function CalcInterval(ByVal StartDateTime As Date, ByVal EndDateTime
As Date) As String
On Error GoTo ERR_CalcInterval
Dim lngInterval As Long
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long
' Interval in seconds
lngInterval = Nz(DateDiff("s", StartDateTime, EndDateTime), 0)
' Subtract the 45 minute delay factor (45 * 60 = 2700)
lngInterval = IIf(lngInterval <= 2700, 0, lngInterval - 2700)
' Calculate hours
lngHours = lngInterval \ 3600
' Calculate minutes
lngMinutes = (lngInterval - (lngHours * 3600)) \ 60
' Calculate seconds
lngSeconds = lngInterval - (lngHours * 3600) - (lngMinutes * 60)
'Now, put it all together
CalcInterval = Trim(str(lngHours)) & ":" & Trim(str(lngMinutes)) & ":" & _
IIf(Len(str(lngSeconds)) = 2, Trim("0" & Trim(str(lngSeconds))),
Trim(str(lngSeconds)))
Exit_CalcInterval:
Exit Function
ERR_CalcInterval:
MsgBox Err.Number, Err.Description
Resume Exit_CalcInterval
End Function
'********* End Code *************
