Report with multiple checkbox criteria

[ttutela]

I need to create a report based on a query which gets input from checkboxes on a form. If user can check multiple checkboxes for 1 field I know I need to build and SQL statement using "or" depending on how many are selected. Is there a way on a form to join together multiple checkboxes into one control so that the control can be queried? Such as frm.control.itemsselected.count?

 

[GrexP]

I just went through this exact same thing last week. If the check boxes are a Control Group in a Frame they act like option buttons, which is really stupid. For most Windows applications the check box is a stand alone control and never part of a group, where if you check one the others become un-checked..

I had to put multiple check boxes on a form and then move them on to a Frame, as opposed to drawing them directly on a Frame. This gives the user the impression that they are grouped together, but they don't act like an Access Option Group.

For the report, I built the query in the query design window with no criteria. Basically, it returned every record. It was 4 tables, 4 joins, and about a dozen fields returned. I then created a report based on that query.

From VBA code I looped through the check boxes (and other controls) and built the WHERE clause for the query. I then passed it as a parameter with the DoCmd.OpenReport method.

DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

The WhereCondition parameter is everything past the WHERE statement in a query.

strSQL = "((Table.Status)="2" Or (Table.Status)="3" Or (Table.Status)="5") AND (Table2.MemID = '321')"

DoCmd.OpenReport "rptMyReport", acViewPreview , , strSQL


I hope this helps.