Checking for any null fields

[Jennifer]

I want to run a query that will display ALL records with ANY null fields.
For example, on addresses, if a state or zip is missing in one record, and
the address in another.

Is there an easy way to do this?

thanks!

 

[KARL DEWEY]

In query design enter Is Null on separate rows below each field you want
to check like this --
Name Address State Zip
Is Null
Is Null
Is Null

 

[John Spencer]

If you mean you want to display any one record where any field is null then
you would put criteria of Is Null under each of the fields but on separate
criteria lines so that the criteria would be OR and not AND.

If you mean something else, you need to explain in more detail.

You could concatenate (add together) all the relevant fields in one
calculated expression and check for is null that way

Field: StreetAddress + City + State + Zip + PhoneNumber
Criteria: Is Null

That will work, because combining fields using the + sign causes the combined
result to be null if any field is null.

You would still need to list the individual fields if you wanted to see the
contents.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

You will need to build a query for each field looking for nulls. Then join
these queries by UNION ALL statements. Below is an example of finding the
nulls in just 2 fields. The ASIF.ID field is the primary key for the table so
what you know which records to look at. The FieldName is the name of the
field so you now which fields are null.

SELECT ASIF.ID, "DATA_SCALE" AS FieldName
FROM ASIF
WHERE ASIF.DATA_SCALE Is Null
UNION ALL
SELECT ASIF.ID, "DATA_PRECISION" AS FieldName
FROM ASIF
WHERE ASIF.DATA_PRECISION Is Null
Order By ASIF.ID;

There is another way to cheat and find it. If you add all the fields
together and use the Len function on each field, it will return a Null value
if even on field is null in the record. Unlike the above, it won't tell you
which is the null field(s).

SELECT ASIF.ID, [ID]+Len([DATA_SCALE])+Len([Owner]) AS Nulls
FROM ASIF
WHERE ((([ID]+Len([DATA_SCALE])+Len([Owner])) Is Null));

Remember that a Null is not the same thing as a record with just spaces in a
field or a strange thing called a Zero Length Field. All three can look like
the field is blank, but only nulls can be found by the Is Null criteria.

 

[Jennifer]

That's what I need!

How do I "increase" the number of criteria lines?

 

[KARL DEWEY]

Scroll down.
--
Build a little, test a little.

That's what I need!

How do I "increase" the number of criteria lines?

 

[Jennifer]

It only gives me 9 fields in the criteria section and there is not a vertical
scroll.

 

[KARL DEWEY]

Open the query in design view, click on VIEW - SQL View, highlight the last
part like this --
.....OR (((Maarkr_1.Auto)=9));
Paste and edit to this ---
.....OR (((Maarkr_1.Auto)=9)) OR (((Maarkr_1.Auto)=10));
When you go back to design view there will be more rows for criteria.

 

[John Spencer]

== SELECT Insert Rows from the menu while in design view

You can insert multiple rows by selecting multiple rows first.
This is tricky as you need to position the cursor at the left edge of the
criteria row (get an arrow pointing to the left when you are in the proper
position) and then drag down (or UP) to select multiple criteria rows.

Once you have done that, select Insert: Rows from the menu and you will have
as many criteria rows added as you have selected.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County