Excluding records, keeping Nulls

[zakary]

Hi folks,
I have a table of around 6000 records comprising 4 fields (A,B,C,D).
- Each field can contain numbers or Nulls.
- Each record can comprise all numbers, a mixture of numbers or Nulls, or all Nulls.

I'd like to build a query that excludes all records that contain any number from a small list of numbers.

This sounds very simple but I am having problems when trying to include records that have Null's in my query output.

For test purposes I tried to exclude all records that contain the numbers 1 or 9 (these numbers can be present in any field).

This works perfectly, in isolation, on Field A (i.e. 1 or 9 but not Null are excluded from field A):

WHERE Table.A Not In (1,9) OR Table1.A Is Null;

When I try to copy the above, referencing fields B-D, I run into problems - no matter how I try to alter the Boolean operators.
I'm starting to get my Not's mixed up with Or's, etc and my head hurts

I'm sure there's a "Doh!" incoming.

 

[zakary]

Thanks Pat, this is much appreciated! Your solution works perfectly across all 4 fields....just what I want. Cheers!