Blank Fields in NOT Null Query Criteria

[Steve Stad]

Why would a few blank fields show up in a query where I am using 'Is Not
Null' in the criteria. The rest of the Nulls are filtered out - but there
are 7 blank fields showing up. I went in to the table and hit delete in each
of the 7 fields but they still show up in the query results.

 

[Jerry Whittle]

There are four things that can cause a 'blank' field. Nulls as you already
surmised.

Next come Zero Length Strings. Basically they are just "". That is a text
string with nothing in it. Technically they are different than a null as null
means that you don't know what goes there whereas a ZLS means nothing goest
there.

Then there are non-printable ASCII characters. One would be a paragraph
return or end of line character.

Lastly there are plain old spaces. Something like might look blank, but
isn't.

When confonted with an empty looking field that isn't working as expected, I
check the data with queries looking for things such as Like " *" ; Null; and
"" .

 

[KARL DEWEY]

A Null is not the same as a 'blank' field. A Null is like a vacuum. If you
add something to a Null the results is Null. Adding something to a blank
gives you the something.

A 'blank' is also known as a zero-lenght string. If you are updating a text
field to remove all data there two ways - replace with a Null or two double
quotes. The two double quotes comprises a zero-lenght string.

Criteria to not show record in either case --
Is Not Null AND <>""

 

[Steve Stad]

Jerry/Karl,

Thank you for replies. The blanks show up using Like "" in qry criteria.
Is there a way to replace these blanks (or ZLS) with something to make it
NULL.

 

[Steve Stad]

Actually I was able to replace the ZLS records with text and then deleted the
text and now they are true NULLS.

 

[KARL DEWEY]

Deleting text makes them zero lenght strings, not nulls.
Use an update query to either have all nulls or zero lenght strings.

Update To: Null
Criteria: ""

Or --
Update To: ""
Criteria: Is Null