Empty Data field that does not return zero lenth

M

Moche

I am trying to filter on a data field that is emply using double quotes "" in
the criteria box which does not work. I have also use Len(Myfield) on the
entire table and get 122 records which I know have data and their lenth but
the other 500 or so records have no lenth returned. What is in these records?
How can I clean them up so that I can use "" and return all these records?
Thanks!
 
J

John Vinson

I am trying to filter on a data field that is emply using double quotes "" in
the criteria box which does not work. I have also use Len(Myfield) on the
entire table and get 122 records which I know have data and their lenth but
the other 500 or so records have no lenth returned. What is in these records?
How can I clean them up so that I can use "" and return all these records?
Thanks!
Click to expand...

NULL - no value, nothing entered, undefined - is not the same as a
zero length string "".

To find records where the field is truly empty, use a criterion of

IS NULL

I believe that 2003 defaults Text field's Allow Zero Length property
to True; previous versions do not, and storing "" into a field will
actually store a NULL.

John W. Vinson[MVP]
 
J

JR Hester

Thanks for your explicit example here. It saved me posting another entry for
this type question. I kept trying Isnull and getting syntax and argument
errors. Never thought of separating the IS from the NULL.
 
J

John W. Vinson

Thanks for your explicit example here. It saved me posting another entry for
this type question. I kept trying Isnull and getting syntax and argument
errors. Never thought of separating the IS from the NULL.
Click to expand...

That's certainly a common source of confusion! There *is* in fact a VBA
function, which can be called from a Query, named IsNull: IsNull(variable)
returns True if variable is a NULL, and false if it has a non-null value.

However, the SQL query syntax (established before there *was* such a thing as
VBA) is

WHERE <field or expression> IS NULL


John W. Vinson [MVP]
 
Top