Help with Avoiding Invalid Use of Null (1 Viewer)

[belly0fdesire]

I am using the following function created in a module:

Public Function StraightTimeLookup(ByVal UName As String, _
                            ByVal dat1 As Date) As Integer

    StraightTimeLookup = DLookup("[StraightTime]", "StraightTime", _
                  "[User] = '" & UName & _
                  "' AND [Date] = #" & dat1 & "#")
End Function

I am then using this as the default value of a field in a form. It is going to lookup what the user puts in as a date and return the value for that user on that date. But if there is nothing entered for that date, it returns an invalid use of Null error. Is there a way around this.

Sorry... I'm still very new at this. Thanks in advance for your help.




PS I would like to have it just return a zero when there is nothing entered for that date...

 

[ejstefl]

If Isnull(dat1) Then
     StraightTimeLookup = 0
Else
     StraightTimeLookup = DLookup("[StraightTime]", "StraightTime", _
                  "[User] = '" & UName & _
                  "' AND [Date] = #" & dat1 & "#")
End If

 

[belly0fdesire]

Okay... NZ() around the formula... I got it.. as usual i figured it out on my own, but thank you to anyone that considered helping me..


EDIT: just saw your response... thank you very much!

 

[Newman]

Public Function StraightTimeLookup(ByVal UName As String, ByVal dat1 As Date) As Integer
    If IsNull(dat1) then
        StraightTimeLookup = 0
    Else
        StraightTimeLookup = DLookup("[StraightTime]", "StraightTime", "[User] = '" & UName & "' AND [Date] = #" & dat1 & "#")
    End if
End Function