How to input criteria to show if this or if this in different fields?

Minddumps

Registered User.
Local time
Today, 19:26
Joined
Jul 5, 2011
Messages
73
I've created a query with intentions to only allow a record to show if it has a null value in any (or more than one) of 6 fields.

I tried intering 'Is Null' in the or section of the query's design view, but I didn't receive the results I was looking for... does anyone know how to do this?


It might help to describe my ending product (on this section):
I have an excel sheet that I mark which fields are missing. In it I have entered the number of missing information there is for each group of records that I have:
zexample.gif

All of the red cells are the count of null values within that record group.

So when I finally get the query working correctly and input into a report, I want to then count each field by each group, but only count the null values just like the example above... anyone know the code for returning the count of only the null values?
 
Last edited:
>>I've created a query with intentions to only allow a record to show if it has a null value in any (or more than one) of 6 fields.


Your post got confusing after that so this is how you accomplish the above. You create your query and then you set up a custom field like this:

NumberOfNulls: iif(isnull([Field1]), 1,0) + iif(isnull([Field2]), 1,0) + iif(isnull([Field3]), 1,0) + iif(isnull([Field4]), 1,0) + iif(isnull([Field5]), 1,0) + iif(isnull([Field6]), 1,0)

Then in the criteria section you put >0 to find all the ones that had a null somewhere. Be sure to replace [FieldN] with the actual field name you want to check.
 
>>NumberOfNulls: iif(isnull([Field1]), 1,0) + iif(isnull([Field2]), 1,0) + iif(isnull([Field3]), 1,0) + iif(isnull([Field4]), 1,0) + iif(isnull([Field5]), 1,0) + iif(isnull([Field6]), 1,0)
Thank you for that, it worked perfectly to filter out only the records that had missing information.

My apologies for the confusion on the second part. Basically I was asking how to then calculate only the null fields when I do the "count" feature in a report.

I count each field by using =Count(*) for example but just as above I only want the number of empty cells.

I hope that explains it better, is that something you may know how to accomplish as well?
 

Users who are viewing this thread

Back
Top Bottom