isnull(myfield) works, but myfield='' does not. why?

  • Thread starter Thread starter BLACKDOG157
  • Start date Start date
B

BLACKDOG157

I have a text field that allows zero length strings, but when I do
searches on it such as:
Where(myField='')
It fails to find anything,
but when I do:
Where(IsNull(MyField))
it finds many records.
Why is this?
 
Null and zero-length string (ZLS) are 2 different things.

If you allow ZLS, then you must check for both values, e.g.:
WHERE (myField Is Null) OR (myField = "")

There are other tricks developers use such as:
WHERE myField & "" = ""
but that is horribly inefficient on an indexed field.

Bottom line is that allowing ZLS is just creating unnecessary work for
yourself, and making the database less efficient. You *must* handle nulls
anyway (e.g. outer join queries), so you are gaining nothing and losing lots
by allowing ZLS.

There is an *extremely* limited set of cases where ZLS is useful. I can
think of only one kind of case (multiple instances, but only one kind of
scenario) where I have used ZLS in the last 2 years, and I develop Access
databases full-time.

If you were having a hard time with Nulls this article discusses how to
handle the 6 most common problems people face with nulls:
http://allenbrowne.com/casu-12.html
 
What Allen Browne said is very true. It's also possible that you don't have
ZLS but possibly spaces or other non-printing characters.

Also try the following with two double-quotes as I've seen stranger things.
Where(myField="")

In a query you can find all three with something like below:

SELECT SpacesNullsZLS.ZeroLengthStrings,
SpacesNullsZLS.Nulls,
SpacesNullsZLS.Spaces
FROM SpacesNullsZLS
WHERE (((SpacesNullsZLS.ZeroLengthStrings)=""))
OR (((SpacesNullsZLS.Nulls) Is Null))
OR (((SpacesNullsZLS.Spaces) Like " *"));
 
One final bit of knowledge.

Allowing Zero Length Strings (zls) does not make the value a zero length
string, it just allows it to be a zls or a null. Unless you specifically
assign a zls to a field it is may very well be null. It will start out as a
null.
 
Back
Top