Populating field with Null value

[rjvega]

I have a database which tracks material being returned from the field. One
of the requests from my users is to be able to autopopulate some of the
fields for material that was returned together. For example, the items may
have the same part number, station (returned from), removal date, dock date,
and waybill, but since they have different serial numbers and may take
different paths once returned, I want individual records.

I created a query which simply returns the part number, station, removal
date, dock date, and waybill for the last item entered in the database. I
then wrote some code to take that data and populate the current form (the
idea being that they autopopulate this data and then manually enter the
serial number). I wrote this behind the On Click event of a button on my
form.

It works perfectly if the fields are populated, but the problem is that
sometimes material does not have a waybill number, so that field is left
blank. When my code runs in this scenario, it returns an error which says
"invalid use of Null".

My code is written as follows where "Last Record" is the name of my query:

Dim WB as String
WB = DLookup("Waybill", "Last Record")
me.Waybill = WB

I tried testing to see if DLookup("Waybill", "Last Record") = Null, but my
syntax must be off because it is not working.

Any help would be appreciated.

 

[Klatuu]

Change WD from String to Variant. Variant is the only variable data type
that can accept a Null value.

If that will not meet your purposes, you can use the Nz function and convert
a Null value to some predefined value. Since WB is a string, you would want
to convert it to a string value. In the example below, it is changed to
**Missing**:
WB = Nz(DLookup("Waybill", "Last Record"), "**Missing**")

 

[Douglas J. Steele]

And just to add onto Dave's response, be aware that you cannot use

If DLookup("Waybill", "Last Record") = Null Then

Null is a special case. It means "Unknown", so you cannot use binary
comparison operators such as = or <> to check whether or not a field has a
Null value. Instead, you must use the IsNull function:

If IsNull(DLookup("Waybill", "Last Record")) = True Then