Only select records where checkbox has been TICKED

[vadharah]

Hello Guys,

I have a form containing several checkboxes of subjects studied. when a user ticks a subject(s) on the click of a button i would like to run a query that selects students with ONLY the selected subjects.

what i have so far is :

SELECT Student.StudentID, Student.Name
FROM Student
WHERE (([forms]![search]![history_A].[value]=Yes) AND ([forms]![search]![maths_A].[value]=Yes) AND ([forms]![search]![science_A].[value]=Yes));

What this is doing is giving me students with ATLEAST one of these courses whereas i want students who have all 3 subjects TOGETHER

Any ideas of enforcing this in the criteria so it only gives me records with 3 ticks only instead of atleast 1?

Thank you in advance

 

[Dennisk]

replace the ANDs with ORs

 

[vadharah]

Dinnisk Thank you for your response.

I have tried what you suggested but still im getting records with unchecked subjects. Is there a way of specifying that if a subject-checkbox hasnt been checked then it wont appear in the results i.e. the results table ONLY contains records with ticks and if only 2 out of three have been ticked it returns only the 2 fields if such dosnt exist then it reurns an empty set?

Is this possible?

 

[Atomic Shrimp]

You need to test the criteria against the fields in the table - at the moment, all you're doing is testing whether the checkboxes on the form are ticked.

So, assuming there are fields in your table named 'history_A', etc, you want something like this:
SELECT Student.StudentID, Student.Name
FROM Student
WHERE ((Student.history_A = Yes) OR (Student.maths_A = Yes) OR (Student.science_A = Yes));

-which will select all records where any combination of the three is checked (change the OR to AND for only those records where all three are checked.

If you want the query to look for just those records that match a set of unbound checkboxes on your form, you want something like this:

SELECT Student.StudentID, Student.Name
FROM Student
WHERE ((Student.history_A = [forms]![search]![history_A].[value]) AND(Student.maths_A = [forms]![search]![maths_A].[value]) AND(Student.science_A = [forms]![search]![science_A].[value]));

Are the checkboxes on the search form bound to a data field anywhere, or are they just checkboxes? (The latter means the above should work - the former means that somewhere, a record is being edited when you try to search, which probably isn't appropriate).