Time Duration Function

[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

 

[Media Lint]

First of all, you are using brackets on variables.

Second, see the DateDiff function.

Third, examine your type declarations.

 

[John Vinson]

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

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