6 Field Query

[andy.s]

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.

 

[Brendan Reynolds]

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.

 

[andy.s]

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

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