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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|