Finding Fields With Null Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a table named "Application" that has lots of records that contain at
least one NULL value in a field.

What I want to do is "Find and Display" only those records that have a NULL
value in a field.

Is it possible to click a button on a form and have the results display in a
report?

Let's call the table "Application"
Let's call the report "Blanks"
Let's call the button "FindBlanks"

Any help on this would be greatly appreciated.

-Sky
 
First, Application is an Access reserved word and you shouldn't use reserved
words for names of objects, fields, etc.

Create a query that includes all the fields in the table. Set the criteria
of each field to:
IsNull
Put the criteria in a different row for each field.
 
PC,

Ok, I changed the name of the Table to Apps.

Now in the query under criterior for Last Name i put IsNull but when i run
the query I just get one blank field. No data shows up.
 
I'm thinking the blank records I'm seeing may not be Null, but zero length
strings instead. How do you test for ZLS?
 
Yea, this is true :-) I have a bigger problem though. I have too many fields
in my table and not enough rows in the criterior section. I think I need to
create a procedure to loop through all the fields testing for NULL. Problem
is I don't have a clue how to write such a procudure.

My guess is use a FOR loop. What do you think? Do you know how to write one?
 
Too many fields?

Sounds like you need to first determine if the design of your table is
normalized. This gets to be sypmtomatic if the number of fields is greater
than 15!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
 
PC,

Your losing me. I don't know the difference between normalized or
non-normalized. I do know that there are 15 fields in my Apps table, not
counting the ID field. Previously those fields were split among different
tables, but my wife had a hissy fit so I combined them all into one table so
she'd shut up ;-)

So I've got these fifteen fields I need to check for Null values on. What now?

-Sky
 
Ask your wife, she seems to be the Access expert! :)

Your original design of multiple tables was probably right. When you said
you had many fields I envisioned a large number and hence my suggestion. A
query has by default 9 criteria rows. You can easily go beyond that by
placing your cursor in one or more of the rows and clicking on Insert - Rows
in the menu at the top of the screen. You need to put Is Null (with a space)
in all 15 fields and each in a different row. So to get 15 criteria rows,
highlight 6 rows and click on Insert - Rows. Then add Is Null for each
field. If you only put it in one or a couple rows and get no records when
you run the query, that just means there are no records with null values in
those fields.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
 
PC,

Your not only helpful, but funny too. Ask my wife :) When it comes to my
wife I feel like Rodney Dangerfield. Sometimes I wish I could wave my hand
and make her disappear, but then I would miss her. Can't live with em', and
can't live without em'.

Insert rows, DUH! I should have thought of that :(

PC, you are the master and I bow to thee. All you MVP's make working with
Access fun. I've learned an awful lot about Access from following your
guidance, but also know there's so much more to learn. Keep up the good work.
Without all of you here to hold us by the hand I'm sure the suicide rate
would be a lot higher, lord forbid...

-Sky
 
Sky,

I have a very extensive library of Access code procedures that I garnered
from here, there and everywhere. They are all in Word and indexed. Most are
in a finished form while some are in rough draft form but usable. You can
copy and paste the code as needed. I sell the CD for $125. If you are
interested, contact me at my email address below.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

BTW, speaking of Can't live with em', and can't live without em' ......

A man and his wife went on a cruise. In an unfortunate turn of events, the
cruise ship sank and the man and his wife were the only survivors having
swam to a remote island. The next morning they put together a plan to go
down to the beach twice a day and look out over the horizon for a passing
ship to signal to rescue them. They stuck to this plan faithfully every day
and before they knew it, it wouldn't be long before it would be the
twentieth aniversary since they arrived on the island. One morning while
they were walking the beach, they spotted a bottle floating in the water. It
looked very unusual so the husband waded in and retrieved the bottle. It had
a cork in it like a vintage wine bottle so the man popped the cork. A haze
rose out of the bottle and a genie appeared. Over and over the genie thanked
the couple for releasing him from his thousand year prison inside the
bottle. In appreciation the genie promised to grant the husband and wife
each one wish. The words were still in the morning air when the wife shouted
with tears running down her face, "I want to go home". Pooh! The wife was no
longer standing beside her husband. The husband was startled and began to
tremble. It was obvious that he was terified at the prospect of being alone
on the deserted island. In desperation and without thinking, the man
stuttered to the genie, "I miss my wife, I wish she was here with me"......
The moral of this story is, "Can't live with em', and can't live without em'
".
 
Back
Top