Query to show empty cells

X

xololady

I am doing a database for addresses and would like to run a query showing the
addresses that are empty. What should I put in the criteria to show an empty
cell?
 
S

S Panja

Try out the query with IsNull as criterion.
--
********* http://panjas.org
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?
 
R

Ron2006

If you are using the query wizard then in the criteria line of the
address field put Null
If that does not pull all of them then add a second criteria line
with ""
Two double quote marks side by side.


If you are writing the query out then write

WHERE IsNull(fieldnameofaddressfield)


Ron
 
X

xololady

Thank you, but that didn't work. I put IsNull in the criteria line for
address, city, state, zip and email and it gave me no records. Any other
suggestions?
 
X

xololady

Thank you! I figured out that I was putting Null in my zip code column, but
it has a default of "0" How do I get rid of that default?
 
S

S Panja

Check Field property in Table design.
--
********* http://panjas.org
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?
 
X

xololady

Thanks!

S Panja said:
Check Field property in Table design.
--
********* http://panjas.org
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?
Click to expand...
 
R

Ron2006

Also, the way you stated your answer it implies that you put the Is
Null on the same criteria line for all of the fields.

This means that ALL of the fields MUST be empty for the record to be
selected. As you found out, if any one of them (you also had it for
ZIP which was NOT null) then the record will NOT be selected.

The way you have it, if someone put in an address line but NOT the
city, state, etc, it would NOT be selected. If that is what you want
that is fine, but you may want to create a different criteria line for
each of the unique combinations that you want to be selected. If you
want any address that has City empty then create a separte criteria
line with is null in the criteria for City and no other criteria. If
you want any that have state empty then yet another criteria line with
is null as criteria for the state only.

In general, I would always make ZIP as a text field. If you ever send
any thing to Canada, it will require letters in the ZIP Code. And if
someone puts in the Zip + 4 you will have to do the formating for it
yourself. Also if the ZIP has leading 00s then they will normally
disappear when you try to print the Zip unless you are using specific
formating to print it, which then gets real complicated if some have
the zip + 4 and some don't.

Ron
 
Top