Time Duration Function

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I'm having trouble creating a function that returns the difference in 2 time
fields. Within a query, below statement returns correct time.

Duration: IIf([BeginDateTime] Is
null,"",Format([BeginDateTime]-1-[EndDateTime],"Short Time"))

Sample Record:

10:40 AM 10:45 AM = 00:05

Below is a function I'm trying to get the same functionality from, but get
errors like "object required" when testing. I think it's in my data type
declarations. Could someone tell me what I'm doing wrong?
====================================================================


Function Duration(BegDate As Variant, EndDate As Variant) As Date

If [BegDate] Is Null Then
Duration = ""
Else
Duration = Format([BegDate] - 1 - [EndDate], "Short Time")
End If

End Function
 
First of all, you are using brackets on variables.

Second, see the DateDiff function.

Third, examine your type declarations.
 
Function Duration(BegDate As Variant, EndDate As Variant) As Date

If [BegDate] Is Null Then
Duration = ""
Else
Duration = Format([BegDate] - 1 - [EndDate], "Short Time")
End If

End Function
Click to expand...

IS NULL is SQL syntax - in this context you need the VBA function
IsNull; and you should be referring to the variables as variables, not
as fields - i.e. leave off the brackets.

Also "" is not a valid value for a Date datatype - a date is a number,
not a string! Use NULL instead.

If IsNull(BegDate) Then
Duration = Null
Else
Duration = CDate(Format(BegDate - 1 - EndDate, "Short Time")))
End If
 
Back
Top