Multiple condition query

[accessma]

Im trying to build a query that checks four fields for blanks. If I try to run the query with the criteria Is Null for all four fields, I only get 27 records. However, if I run the query with the condition only on one field I get the right number of records around 1000. I have been having to run four independant queries and then merge them in Excel. What am I doing wrong?

 

[pbaldy]

At a guess, in SQL view you have "AND" between each test and you really want "OR". In the design view, that means you have them all on the same line instead of each on a different criteria line.

 

[tehNellie]

How is your WHERE clause defined? It sounds like you might have:

WHERE [column1] is null [b] AND[/b] [column2] is null 
[b]AND[/b] [column3] is null [b]AND[/b] [column4] is null

Which will only returns the rows where all four columns are null.

If you try:

WHERE [column1] is null [b]OR[/b] [column2] is null 
[b]OR[/b] [column3] is null [b]OR[/b] column4 is null

Then you should see all rows where any one (or more) of the columns contain a Null entry.

 

[accessma]

OK, let me rephrase the situation. I am looking for records that have one of four fields being null. So lets say I want to find where filed 1 is null and the other three have data, next, I want to find where field 2 is null but 1,3, and 4 have data, then the same for fields 3 and 4. Then a way to sort them so that if all fieds are blank they show first, and so on.

 

[tehNellie]

WHERE [column1] is null [b]OR[/b] [column2] is null 
[b]OR[/b] [column3] is null [b]OR[/b] column4 is null
ORDER BY [column1],[column2],[column3],[column4]

Should do it I believe, NULL will sort to the top of your list so you'll get all null rows first, then all column1 nulls, all column2 nulls, all column3 nulls, all column4 nulls.

 

[accessma]

OK thanks. Will give it a go.

 

[EMP]

I am looking for records that have one of four fields being null. So lets say I want to find where filed 1 is null and the other three have data, next,...................
...................
Then a way to sort them so that if all fieds are blank they show first, and so on.

It's rather confusing and not clear. When it's one, it can't be all.

^

 

[accessma]

Thanks techNellie, that got what i wanted.