Query to find null values

[Guest]

I have a table that includes names and addresses. I know some of the “Stateâ€
data is missing, therefore I tried running a query to give me all records
where the ‘State’ field is null. The query does not return any data. I ran
a query to give me all records where the ‘State’ field is ‘not null’; the
result set included ‘State’ fields with actual states and those that are null.

If you look at the table, you can see there is no data in the ‘State’ field
for some of the records. I checked to see if maybe there were spaces in the
‘State’ field throwing off the query, but there are no spaces in the State
field. I have used queries like this hundreds of times before without a
problem. I have to be overlooking something. Any ideas? Thanks in advance.

 

[Guest]

Did you check for zero-length string (ZLS) in the State field? Does your
State field allow ZLS?

SELECT Lastname, Firstname, State FROM MyTable
WHERE State = ""

Also, see "Fields: Allow Zero Length" in this article by Access MVP Allen
Browne:
http://allenbrowne.com/bug-09.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a table that includes names and addresses. I know some of the “Stateâ€
data is missing, therefore I tried running a query to give me all records
where the ‘State’ field is null. The query does not return any data. I ran
a query to give me all records where the ‘State’ field is ‘not null’; the
result set included ‘State’ fields with actual states and those that are null.

If you look at the table, you can see there is no data in the ‘State’ field
for some of the records. I checked to see if maybe there were spaces in the
‘State’ field throwing off the query, but there are no spaces in the State
field. I have used queries like this hundreds of times before without a
problem. I have to be overlooking something. Any ideas? Thanks in advance.

 

[Guest]

Additionally there might also be spaces or some nonprinting ASCII characters.
Something like below might also catch them.

SELECT Lastname, Firstname, State
FROM MyTable
WHERE State Not Like "[A_Z]*"
OR State is Null;

 

[Guest]

In this database the field does allow ZLS. Using Where = Ҡ- I was able to
find the records I needed. I also read the link you provided. However, I
had a follow-up question. Since I have never ran into this problem before,
is there something about the way that the data was imported into this
particular database that could have created this ZLS condition? The data in
this database was imported from Excel.

I checked several other databases (where the data was imported from a text
file) and found that Allow Zero Length String is set to “Yesâ€. In these
databases, I can use “null†in the query and the nulls are returned without
an issue. Any ideas? Thanks (again) in advance.

 

[Guest]

As Allen Browne mentions, Allow zero length defaults to Yes for new fields. I
suspect that at one time your State field had this default property set.
Changing this setting to No only restricts future data entry. It does not
nullify existing records with a ZLS.

If the field initially included a default setting of AllowZeroLength = Yes
and the required property was set to Yes, then a ZLS results if one presses
the spacebar. You can see a table in Access Help that includes the results of
various combinations of these settings, by clicking your mouse cursor into
the AllowZeroLength property and then pressing the F1 key to bring up context
sensitive Help (this works in Access 2002, so I suspect it should work in
2003 as well). Without running some import tests using an Excel spreadsheet,
you might be getting a similar result. I'll leave it up to you to test the
various combinations of Allow ZLS and Required values when importing data.
Please feel free to reply with the results of such tests. You might want to
run the test on two columns in Excel: one specifically formatted as text and
the other with a General format.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

In this database the field does allow ZLS. Using Where = Ҡ- I was able to
find the records I needed. I also read the link you provided. However, I
had a follow-up question. Since I have never ran into this problem before,
is there something about the way that the data was imported into this
particular database that could have created this ZLS condition? The data in
this database was imported from Excel.

I checked several other databases (where the data was imported from a text
file) and found that Allow Zero Length String is set to “Yesâ€. In these
databases, I can use “null†in the query and the nulls are returned without
an issue. Any ideas? Thanks (again) in advance.

 

[Guest]

You've been very helpful. Thank you!