For some reason I can't get my head round this so any help is much
appreciated!

I want to create a query based on 6 fields. I want the query to include
the record if more than 1 of the fields is not null.

Therefore if 2 of those fields contain a value include the record, if 6
of the fields contain a value include the record, if 1 of the fields
contains a value do not return the record.

SELECT tblTest.*
FROM tblTest
WHERE
(((IIf(IsNull([Field1]),0,1)+IIf(IsNull([Field2]),0,1)+IIf(IsNull([Field3]),0,1)+IIf(IsNull([Field4]),0,1)+IIf(IsNull([Field5]),0,1)+IIf(IsNull([Field6]),0,1))>1));

This isn't very efficient, as the database engine will not be able to make
use of any indexes on those fields. There could be performance problems if
there are large numbers of records involved.

--
Brendan Reynolds
Access MVP

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> For some reason I can't get my head round this so any help is much
> appreciated!
>
> I want to create a query based on 6 fields. I want the query to include
> the record if more than 1 of the fields is not null.
>
> Therefore if 2 of those fields contain a value include the record, if 6
> of the fields contain a value include the record, if 1 of the fields
> contains a value do not return the record.
>

Thanks Brendan, works a treat on 10,000 records.

It was one of those things that was making my head hurt!