Dlookup getting Run time error '94' Invalid use of nulls

[smsmail]

Hi

I am getting a run time error on the dlookup code, because no record meet the criteria, therefore the dlookup returned a null value.

Dim lngLogID As Long
    
    lngLogID = DLookup("[LogID]", "Download_Log", "[JobNumber] = '" & Me.JobNumber & "' and [ClientID]= " & Me.clientid)
      
    Debug.Print lngLogID
    
    If Not IsNull(lngLogID) Then
       DoCmd.OpenForm "frmdownloadlog", , , "logid = " & lngLogID, acEdit
    Else
       DoCmd.OpenForm "frmdownloadlog", , , , acEdit
    End If

[code/]

Is there a simple way of handling this?

Thanks for your help

 

[MajP]

dlookup returns a Null if not found. You cannot assign a long variable to null

Dim lngLogID As variant

 

[MajP]

you can also wrap the dlookup in a null to zero (NZ) function

lngLogID = NZ(DLookup("[LogID]", "Download_Log", "[JobNumber] = '" & Me.JobNumber & "' and [ClientID]= " & Me.clientid),0)
...
If lngLogID <> 0 Then

 

[smsmail]

Thanks MajP. Your help is very much appreciated~!

 

[MajP]

FYI

Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.

If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.

If more than one field meets criteria, the DLookup function returns the first occurrence.

 

[SteveHagan]

Hi Smsmail

Take a look at Allen Browne's Elookup function...

http://allenbrowne.com/ser-42.html