Query Criteria from Multiple Checkboxes

[NoelMG]

Hi all

I have been trying for weeks with no success. I hope someone on this wonderful forum can help!

Basically I have a form that has 5 checkboxes - BrandDirector, Marketing, Stakeholder, Aftersales and Sales. There are checkbox fields in a table with the same names.

Basically, the user would like to tick the boxes on the form to generate a report with those criteria. For example, a record could have Marketing and Sales ticked, but when the user generates the report they would want for instance everybody who has Marketing ticked. This sounds pretty simple, but I haven't been able to find a way. My current SQL looks like this:

SELECT qryMainFormQuery.CurrentExStaff, qryMainFormQuery.BrandDirector, qryMainFormQuery.Marketing, qryMainFormQuery.Stakeholders, qryMainFormQuery.Aftersales, qryMainFormQuery.Sales
FROM qryMainFormQuery
WHERE (((qryMainFormQuery.CurrentExStaff)="Current") AND ((qryMainFormQuery.BrandDirector)=[Forms]![frmSelectInvitees]![BrandDirector])) OR (((qryMainFormQuery.CurrentExStaff)="Current") AND ((qryMainFormQuery.Marketing)=[Forms]![frmSelectInvitees]![Marketing])) OR (((qryMainFormQuery.CurrentExStaff)="Current") AND ((qryMainFormQuery.Stakeholders)=[Forms]![frmSelectInvitees]![Stakeholders])) OR (((qryMainFormQuery.CurrentExStaff)="Current") AND ((qryMainFormQuery.Aftersales)=[Forms]![frmSelectInvitees]![Aftersales])) OR (((qryMainFormQuery.CurrentExStaff)="Current") AND ((qryMainFormQuery.Sales)=[Forms]![frmSelectInvitees]![Sales]));

Trouble is that this code also brings up people who aren't ticked to anything, as a result of the criteria I have used.

I know it's probably something in the query criteria, but I can't figure it our!

Help would be greatly appreciated!

Thanks in advance,
Noel.

 

[PHV]

Have you tried something like this ?
SELECT CurrentExStaff, BrandDirector, Marketing, Stakeholders, Aftersales, Sales
FROM qryMainFormQuery
WHERE CurrentExStaff='Current' AND
( BrandDirector=[Forms]![frmSelectInvitees]![BrandDirector]
OR Marketing=[Forms]![frmSelectInvitees]![Marketing]
OR Stakeholders=[Forms]![frmSelectInvitees]![Stakeholders]
OR Aftersales=[Forms]![frmSelectInvitees]![Aftersales]
OR Sales=[Forms]![frmSelectInvitees]![Sales]
);


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[NoelMG]

Hi PH

I've just tried your SQL, but unfortunately this brings up everybody regardless of whether or not the boxes are ticked on their record.

Possibly because when the boxes are blank, it's recorded as a "No", so it's saying pick up the field if it's yes or no?

Thanks,
Noel.

 

[FancyPrairie]

Check out this FAQ faq181-5497 It will return the where clause (without the word where). It works for 0 to many single and multiselect list boxes, comboboxes, text boxes, ranges, options groups, and check boxes. You only have to do 3 things to make it work.

1. Open a new module and copy and paste the function into the new module.
2. Set the Tag property of your check boxes according to the directions in the FAQ. For example, the Tag property for the Marketing checkbox would look like this: Where=[qryMainFormQuery].[Marketing],CheckBox,=,True;
3. Open the report as specified in the FAQ

 

[PHV]

And this ?
SELECT CurrentExStaff, BrandDirector, Marketing, Stakeholders, Aftersales, Sales
FROM qryMainFormQuery
WHERE CurrentExStaff='Current' AND
( (BrandDirector=True And BrandDirector=[Forms]![frmSelectInvitees]![BrandDirector])
OR (Marketing=True And Marketing=[Forms]![frmSelectInvitees]![Marketing])
OR (Stakeholders=True And Stakeholders=[Forms]![frmSelectInvitees]![Stakeholders])
OR (Aftersales=True And Aftersales=[Forms]![frmSelectInvitees]![Aftersales])
OR (Sales=True And Sales=[Forms]![frmSelectInvitees]![Sales])
);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244