IsNull columns

[Joyce Castro]

I have a table in Acces 2007 that has 24 columns that can contain null values. I want the records that have the null values to display. I am kind of new to this so any help I can get would be appreciated.

The columns I am trying to get the null values from are titled 1st Rating, 2nd rating, 3rd rating, etc. I have tried the IsNull, but it only returns the rating for the 1st rating and not the other columns.

Can you please tell me how to do this?

[Rabbit]

If you have no where condition that limits the display of nulls, then nulls will display.

[NeoPa]

Joyce, your question makes no discernable sense. Are you saying you want to show only those records where there is at least one column that is Null?

[Mihail]

I think so, NeoPa.
And I "see" only one solution.
I am sure that is a good one if all the fields are numeric:

Create a new column (i.e "N" ) in your query based on SUM formula (sum all fields where you are looking for null values N = F1+F2+...Fk). If in a record you have one ore more null values in fields F1, F2 .... then you obtain a null value in field "N". If there are not null values in the record, the "N" field will be not null.
Now you can use IsNull in field "N".

Hope this is a help for you.

Maybe others can explain how to use the same logic for fields with non numeric values.

Good luck !

[NeoPa]

If we assume that's right for now Mihail, then your solution would indeed work. The string equivalent (which can also be used for non-string fields too) woul be, strangely enough, exactly the same ;-) While [X] & [Y] & [Z] would return Null only if all the values were Null, [X] + [Y] + [Z] gives a string result (unless all fields are defined as numeric) if there are no Null values, but a Null if any are Null (See Using "&" and "+" in WHERE Clause).

All that said, I would recommend simply checking each field individually and using OR between each result :

WHERE ([X] Is Null)
   OR ([Y] Is Null)
   OR ([Z] Is Null)

This is easier (and quicker) for the SQL engine to process.

That said, knowing why your suggestion works is always a good understanding to have ;-)