NULL date error in VB code for Access 1

[falcon7a]

I am trying to look into an Access table. If certain conditions are met, I want the variable CompactorDate to store the date. The code works fine if the DLookup function returns a date. The program errors out if DLookup returns Null. The IF loop doesn't seem to solve the problem either. The error is: "Invalid use of Null"

Dim CompactorDate As Date
If CompactorDate = Null Then
CompactorDate = "1/11/1999 10:00:00 PM"
Else
CompactorDate = DLookup("EPCTimeStamp", "qryTempData", "EPCStatusDesc='Compactor' ")
End If

Any ideas on how I can solve the Null problem?
Thanks

 

[RoyVidar]

This forum is dedicated VB and databases, and your question seems to be related to Access - I think DLookup is a native Access function. There are 7 Access fora to choose from (do a forum search at the top) for future questions relating only to Access.

The Date datatype cannot be set to Null, only variables of datatype Variant can. The if test fails for at least two reasons

1 - after declaration of a date variable, it's instantiated as 0 (the number 0 -> 30/12/1899)
2 - Null isn't equal to anything, not even itself, so comparison with = Null will return false. On fields or controls one may use the NZ function (native to Access) or IsNull to check more reliably.

What you could do here, is use NZ

[tt]CompactorDate = NZ(DLookup("EPCTimeStamp", _
"qryTempData", "EPCStatusDesc='Compactor' "),0)[/tt]

This should assign 0 if no date is found, or the actual date if one is found, then check against 0 afterwards.

Roy-Vidar

 

[jebenson]

Use the IsNull function. Instead of:

If CompactorDate = Null Then

use

If IsNull(CompactorDate) Then


Later in the code you need to test the value returned by DLookup for Null before assigning to CompactorDate:

If Not IsNull(DLookup("EPCTimeStamp", "qryTempData", "EPCStatusDesc='Compactor' ") Then
CompactorDate = DLookup("EPCTimeStamp", "qryTempData", "EPCStatusDesc='Compactor' ")
Else
'Code here to handle Null value
EndIf

Hope this helps.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[Sheco]

A date field from a database can be null, but a undefined date variable is never null.

Example:

Dim dMyDate As Date
If IsNull(dMyDate) Then
'This will never happen
MsgBox "I hate VB"
Else
'This will always happen
MsgBox "I love VB"
End If

Here is one way to check for an unititialized date variable:

Dim dMyDate As Date, dFakeDate as Date
If dMyDate = dFakeDate Then
MsgBox "Date is not initialized"
Else
'This will always happen
MsgBox "Date has been set"
End If