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.

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
"" .
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Steve Stad" wrote:

> 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.

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 <>""

--
Build a little, test a little.


"Steve Stad" wrote:

> 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/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.

"KARL DEWEY" wrote:

> 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 <>""
>
> --
> Build a little, test a little.
>
>
> "Steve Stad" wrote:
>
> > 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.

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

"Steve Stad" wrote:

> 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.
>
> "KARL DEWEY" wrote:
>
> > 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 <>""
> >
> > --
> > Build a little, test a little.
> >
> >
> > "Steve Stad" wrote:
> >
> > > 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.

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


--
Build a little, test a little.


"Steve Stad" wrote:

> Actually I was able to replace the ZLS records with text and then deleted the
> text and now they are true NULLS.
>
> "Steve Stad" wrote:
>
> > 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.
> >
> > "KARL DEWEY" wrote:
> >
> > > 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 <>""
> > >
> > > --
> > > Build a little, test a little.
> > >
> > >
> > > "Steve Stad" wrote:
> > >
> > > > 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.