Prev: Access 2K2: making numbers up in a query sum
Next: A Great Big Thank You!
From: Steve Stad on 27 May 2010 16:51
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.
From: Jerry Whittle on 27 May 2010 17:56
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.
From: KARL DEWEY on 27 May 2010 18:31
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.
From: Steve Stad on 28 May 2010 08:28
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.
From: Steve Stad on 28 May 2010 08:48
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.
 |  Next  |  Last
Pages: 1 2
Prev: Access 2K2: making numbers up in a query sum
Next: A Great Big Thank You!