Function getting error when field empty

[Bullsandbears123]

I have a function(below) I use in a query. The inputs are from other fields, but if the number field is empty, I get an "error#" as output(I need it empty). Any ideas on how to fix this.


Function nextdate(Optional firstdate As Date, Optional intervaltype As String, Optional number As Integer = 0) As Date
On Error GoTo handle

If number = 0 or ismissing(number) or isempty(number) or isnull(number) Then
nextdate = Empty

Exit Function
Else
Dim mydate As String
Dim myday

If LCase(intervaltype) = "m-f" Then
myday = LCase(WeekdayName(Weekday(firstdate)))

If myday = "friday" Then
nextdate = DateAdd("d", 3, firstdate)
Exit Function
Else
nextdate = DateAdd("d", 1, firstdate)
Exit Function
End If

ElseIf number > 0 Then
mydate = Format(firstdate, "m/d/yyyy&quot
nextdate = DateAdd(intervaltype, number, mydate)
Else
nextdate = Empty

End If

Exit Function
End If

handle:
msgbox Err.Description

End Function

 

[LearningSql]

Just a quick thought (a very quick thought) -- can you set your variable to "null" instead of empty?

If number = 0 or ismissing(number) or isempty(number) or isnull(number) Then nextdate = Null

If I think of this any more I'll send you back more thoughts.

PA

 

[Bullsandbears123]

I tried it, no luck. Any ideas are always welcome.

THANKS!

 

[TelcoBT]

I think you need to return a variant from your function, as otherwise it is expecting a date variable.