Filter yes/no field

[Guest]

I have a linked table that contains 4 yes/no fields.

Since this is a linked table from another application I cannot control the
data being entered.

The proper data entry would be to only have one and only one box checked yes.

I need to create a query which will return records where more than one box
is checked yes or all boxes are checked no.

 

[Douglas J. Steele]

Boolean fields in Access have a value of -1 if True, or 0 if False.

While it may not seem as though it makes sense, add a computed field to your
query that adds the four boolean fields together.

You want any records where the sum isn't equal to -1.

 

[John Spencer (MVP)]

One method, that is convenient but possibly slower

SELECT T.*
FROM TheLinkedTable As T
WHERE Abs(CInt(Field1) + Cint(Field2) + Cint(Field3) + CInt(Field4)) <> 1

You can probably get away without using CINT to force the conversion of your
fields to a number, but it wont hurt.

If this doesn't work or is to slow then you might try
SELECT T.*
FROM TheLinkedTable as T
WHERE
(Field1 = False and Field2=False and Field3 = false and Field4 = false)
OR (Field1 = True and Field2 = True)
OR (Field1 = True and Field3 = True)
OR (Field1 = True and Field4 = True)
OR (Field2 = True and Field3 = True)
OR (Field2 = True and Field4 = True)
OR (Field3 = True and Field4 = True)

That takes care of all the case where NO field is checked and all the cases
where at least two fields are checked.