Query Criteria based on Form Multiple Checkbox Selection

[pccarrick]

I am trying to build a query in Access 2003 based upon a form that the users select's several checkbox's then displays the results in the query.

For example if i have a table like

Apples Pears Oranges Grapes
N Y N Y
Y N N N
Y Y Y N

When say apples is selected it will display all the rows which are true, if i select pears it will do the same. When i select apples and pears it will only display the rows that have both values to be true and not the one that only has the value true for one column. i have tried using the AND in my query and also OR but can't get it to do it how i want it. Any help would be appreciated as i have look and look and still can't get this to do what i want.

Thanks in advance

 

[PWise]

method 1

Select field1,field2,..
from Tablename
Where Apples='y'
Union
Select field1,field2,..
from Tablename
Where Pears='y'
Union
Select field1,field2,..
from Tablename
Where Oranges ='y'
Union
Select field1,field2,..
from Tablename
Where Grapes='y'

method 2

Select field1,field2,..
from Tablename
Where 'y' in(Apples, Pears, Oranges ,Grapes)

 

[PHV]

When i select apples and pears it will only display the rows that have both values to be true
SELECT * FROM yourTable
WHERE (Apples=Forms!yourForm!ckboxApples OR Forms!yourForm!ckboxApples IS NULL)
AND (Pears=Forms!yourForm!ckboxPears OR Forms!yourForm!ckboxPears IS NULL)
AND (Oranges=Forms!yourForm!ckboxOranges OR Forms!yourForm!ckboxOranges IS NULL)
AND (Grapes=Forms!yourForm!ckboxGrapes OR Forms!yourForm!ckboxGrapes IS NULL)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886