Time difference

[ImChard]

I have a table with a couple fields in it for entering times: [Resp_Notified] and [Resp_Arrival]. In the table the property is set to Date/Time , format set to Short Date and mask set to 00:00;0;_

In the query I have -

DeltaT: DatePart("n",timeduration([Resp_Notified],[Resp_Arrival]))

to get the difference in minutes

Anyways it works good if there is a value in both fields but if one of the fields has nothing entered in it the whole query bombs and I get an error "Data Type Mismatch in Query Expression"

Ideas?


Here the function coding:

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
Optional blnShowdays As Boolean = False) As String

' Returns duration between two date/time values
' in format hh:nn:ss, or d:hh:nn:ss if optional
' blnShowDays argument is True.

' If 'time values' only passed into function and
' 'from' time is later than or equal to 'to' time, assumed that
' this relates to a 'shift' spanning midnight and one day
' is therefore subtracted from 'from' time

Dim dtmTime As Date
Dim lngDays As Long
Dim strDays As String
Dim strHours As String

' subtract one day from 'from' time if later than or same as 'to' time
If dtmTo <= dtmFrom Then
If Int(dtmFrom) + Int(dtmTo) = 0 Then
dtmFrom = dtmFrom - 1
End If
End If

' get duration as date time data type
dtmTime = dtmTo - dtmFrom

' get whole days
lngDays = Int(dtmTime)
strDays = CStr(lngDays)
' get hours
strHours = Format(dtmTime, "hh")

If blnShowdays Then
TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
Else
TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "00") & _
Format(dtmTime, ":nn:ss")
End If

End Function

 

[SkipVought]

hi,

DeltaT: DatePart("n",ABS([Resp_Notified]-[Resp_Arrival]))

Duration is simple the difference of two dat/time values. That's all! Of course that difference is DAYS duration, which can be simply be converted to HOURS or MINUTES using 5th grade arithmetic or using the DateDiff function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ImChard]

Thank You

 

[MacroScope]

Why use DatePart? Why not DateDiff? It's designed specifically to provide a time-date difference.