A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Blank Fields in NOT Null Query Criteria



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 09:51 PM posted to microsoft.public.access.queries
Steve Stad
external usenet poster
 
Posts: 89
Default Blank Fields in NOT Null Query Criteria

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.
Ads
  #2  
Old May 27th, 2010, 10:56 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Blank Fields in NOT Null Query Criteria

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.

  #3  
Old May 27th, 2010, 11:31 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Blank Fields in NOT Null Query Criteria

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.

  #4  
Old May 28th, 2010, 01:28 PM posted to microsoft.public.access.queries
Steve Stad
external usenet poster
 
Posts: 89
Default Blank Fields in NOT Null Query Criteria

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.

  #5  
Old May 28th, 2010, 01:48 PM posted to microsoft.public.access.queries
Steve Stad
external usenet poster
 
Posts: 89
Default Blank Fields in NOT Null Query Criteria

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.

  #6  
Old May 28th, 2010, 03:19 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Blank Fields in NOT Null Query Criteria

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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.