Is Not Null

[Mike P]

I am running a query that should only produce records in which FieldX Is Not
Null. However, records in which FieldX’s look Null are showing up in the
query.

In my module which populates the table with FieldX, I have a variable that
is set to
strFieldX = “â€
strFieldX may or may not get string data appended to it while processing. At
the end of my processing loop, I have
Rec.FieldX = strFieldX

Is it possible that Ҡis equal to something other than Null? If so, any
ideas on how to make a blank strFieldX load the value of Null in rcd.FieldX
when strFieldX = “â€
Otherwise, I keep getting records that appear to be Null showing up with I
use the criteria of Is Not Null.

Thanks in advance for your help.

 

[John Spencer]

You are correct. "" is not null it is a zero-length string.

You can use criteria

Field: Whatever
Criteria: is not Null and <> ""

or change your procedure to

If strFieldX <> "" Then Rec.FieldX = strFieldX

Or if you really want to be sure that Rec.Fieldx is null

IF strFieldX = "" Then
Rec.FieldX = NULL
ELSE
Rec.FieldX = strFieldX
END IF

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Dirk Goldgar]

I am running a query that should only produce records in which FieldX Is
Not
Null. However, records in which FieldX’s look Null are showing up in the
query.

In my module which populates the table with FieldX, I have a variable that
is set to
strFieldX = “â€
strFieldX may or may not get string data appended to it while processing.
At
the end of my processing loop, I have
Rec.FieldX = strFieldX

Is it possible that Ҡis equal to something other than Null?

Yes. The zero-length string "" is *never* equal to Null. Null is a "no
data" condition, "" is data that happens to have zero-length.

If so, any
ideas on how to make a blank strFieldX load the value of Null in
rcd.FieldX
when strFieldX = “â€

Instead of

Rec.FieldX = strFieldX

.... how about using this:

If Len(strFieldX) > 0 Then
Rec.FieldX = strFieldX
Else
Rec.FieldX = Null
End If

If you find yourself using this sort of logic in lots of places, you can
write a function to do it:

'----- start of code -----
Function ZLStoNull(sValue As String) As Variant

If Len(sValue) > 0 Then
ZLStoNull = sValue
Else
ZLStoNull = Null
End If

End Function
'----- end of code -----

Then you could call that function wherever you make these assignments:

Rec.FieldX = ZLStoNull(strFieldX)

You can run an update query to fix up those records that already have
zero-length strings where you'd rather have Null:

UPDATE YourTableName
SET FieldX = Null
WHERE FieldX = "";

Otherwise, I keep getting records that appear to be Null showing up with I
use the criteria of Is Not Null.

I would also recomment setting the field's AllowZeroLength property (in
table design) to No. You would do this after having run the update query
above. Bear in mind that doing so will cause an error to be raised any time
you try to store "" in the field, so it will cause errors in existing code
that assigns "" to the field. That's good, if your intention is henceforth
to prevent that from happening.

An alternative to this fix is to allow zero-length strings in your code, but
change your queries to filter on Null OR ZLS. You can do that in one handy
criterion by using the concatenation operator, like this:

WHERE FieldX & "" <> ""

However, I don't like this approach. I prefer that if a zero-length string
is to be treated the same as Null -- which is most of the time, in my
applications -- I just don't let the ZLS get into the field in the first
place, and use only Null to represent the no-data condition.

 

[Brent Spaulding \(datAdrenaline\)]

In addition to the excellent advice/information given, I would like to add
the following with respect to data input and the ZLS (Zero Length String)
and Null ...
------------------------------
When you enter "" (A ZLS)
- in a numeric field that IS NOT required, a Null will be stored.
- in a numeric field that IS required, you get an error.
- in a text field that allows ZLS's, a ZLS will be stored.
- in a text field that IS NOT required and DOES NOT allow ZLS's, a Null will
be stored.
- in a text field that IS required and DOES NOT allow ZLS's, you get an
error
- in an UNBOUND control, a Null is returned by the control