Is Not Null?

[Tony Williams]

I have a query where I want to use Is Not Null in the criteria to check
whether there is data in a text field. However when I run the query I still
get all the records. Anyone tell me why?
Thanks
tony

 

[Carl Rapson]

I have a query where I want to use Is Not Null in the criteria to check
whether there is data in a text field. However when I run the query I still
get all the records. Anyone tell me why?
Thanks
tony

If the field contains an empty string ("") instead of Null, it won't match
the Is Not Null condition. Try this instead:

Nz([field],"") <> ""


Carl Rapson

 

[Michel Walsh]

They may have a string of zero characters, which, in Jet, is not a NULL (but
which some other databases would consider the same thing as a null; just in
case you imported the data).

0 <> LEN( fieldName & "" )

will handle the situation, but without the use of indexes. You can add a
TRIM to be sure you eliminate also the possibility of a multiple characters,
all blanks:


0 <> LEN( TRIM(fieldName & "" ))


Maybe a better solution is to update these zero length string by a NULL, and
then, you will be able to use the simpler solution:


fieldName NOT IS NULL



Vanderghast, Access MVP

 

[Tony Williams]

Thanks Carl worked fine
Tony

I have a query where I want to use Is Not Null in the criteria to check
whether there is data in a text field. However when I run the query I
still get all the records. Anyone tell me why?
Thanks
tony

If the field contains an empty string ("") instead of Null, it won't match
the Is Not Null condition. Try this instead:

Nz([field],"") <> ""


Carl Rapson

 

[Tony Williams]

Thanks Michel I'll compare your ideas with those of Carl's
Thanks again
Tony