DLookup Problem with Null

[RonMcNatt]

I am using a DLookup function to check for a date in a table. I have set up this simple if-then statement to execute based on the results (I'll build a more complex if-then later).

The problem is that the DLookUp never seems to equal null so I always get the message "Date Found". If I print out the value of varX (in a MsgBox) it doesn't show any text for dates that don't exist, but I still get the "Date Found" messaage.

Any suggestions?



Dim varX as Variant

'Check to see if data already exists for date
varX = DLookup("DEL_DATE", "mTIM1", "DEL_DATE = #" & startDate & "#&quot
If varX = Null Then
MsgBox "The Date was not Found"
Else
MsgBox "Date Found"
End If

 

[lhite]

Instead of looking for Nulls, use a date that clearly won't be in your table, such as:

Dim varX as Variant

'Check to see if data already exists for date
varX = DLookup("DEL_DATE", "mTIM1", "DEL_DATE = #" & startDate & "#&quot
If varX < 01/01/1900 Then
MsgBox &quot;The Date was not Found&quot;
Else
MsgBox &quot;Date Found&quot;
End If

 

[JohnLowell]

Try and chnage the function to this:

Dim varX as Variant

'Check to see if data already exists for date
varX = DLookup(&quot;DEL_DATE&quot;, &quot;mTIM1&quot;, &quot;DEL_DATE = #&quot; & startDate & &quot;#&quot
If isnull(varX) Then
MsgBox &quot;The Date was not Found&quot;
Else
MsgBox &quot;Date Found&quot;
End If