Queries Based On Check Boxes

[Rubz]

I'm trying to run a query based on an unbound form with a number of check boxes, but have run into a problem that hopefully someone can help with:



I have a single table with data being added via a form. The data is text comments on a number of resources, then a number of check boxes to show which resources the comments refer to (Resource1, Resource2, Resource3 etc). I want to be able to produce a query on the comments referring to any of the resources.

To do this I have a query with the comments and each of the check boxes. I have another form that is a copy of all the check boxes on the first form, but they are all unbound. The criteria in the query for each check box is then taken from the unbound form check boxes. The plan is that my staff tick the box(es) that they want the query to be on and this runs that query.

The problem is that while the criteria in the query selects only the records that are checked (ie when Resource1 is checked on the unbound form the query just selects records with Resource1 checked in the data) it also means it doesn't select records unless they are also unchecked for other resources (ie a record checked for Resource1 and Resource2 will not be selected as the query recognises Resource2 as being unchecked in the unbound form).

I'm after a way of getting the query to recognise checked check boxes (yes), but for unchecked check boxes to allow records that are either checked or unchecked (yes or no) (ie when ticking Resource1 and leaving Resource2 unticked in the unbound form the query will select all records checked for Record1 whether they are checked for Record2 or not).

Hope this makes some sort of sense, and thanks to anyone with answers.

[ConneXionLost]

Copy the SQL code from your query and post it.

[Rubz]

No SQL so far. Simply added [Forms]![ReportForm]![Resource1] (or Resource2, Resource 3 etc) to the criteria under each field in the query.

Did try using IIf([Forms]![ReportForm]![Resource1]=Yes,Yes,???) in the criteria, but couldn't work out what to put in at ??? to make it work.

[ConneXionLost]

Open your query in design view.

On the Query design toolbar, the first icon should be a view button with a pulldown arrow next to it. Click the pulldown arrow and select SQL view. Copy the SQL code, and post it.

[Rubz]

Goes on quite a bit due to the number of resources, but here it is:

SELECT [Red Book].Ref, [Red Book].Name, [Red Book].Contact, [Red Book].Date, [Red Book].Comment, [Red Book].Beavers, [Red Book].Cubs, [Red Book].Scouts, [Red Book].Explorers, [Red Book].Network, [Red Book].[Badges - Current], [Red Book].[Badges - New], [Red Book].Programme, [Red Book].[Certificate of Excellence], [Red Book].[Nights Away], [Red Book].Resources, [Red Book].[The Colony Essentials], [Red Book].[The Colony Programme], [Red Book].[The Colony Programme Plus], [Red Book].[The Beaver Scout Logbook], [Red Book].[BS Badges and Award poster], [Red Book].[BS Wallchart], [Red Book].[BS Record Card], [Red Book].[BS Well Done Certificate], [Red Book].[BS Moving On Certificate], [Red Book].[BS Investiture Certificate], [Red Book].[BS Top Award Certificate (CS Bronze)], [Red Book].[BS Games Book], [Red Book].[Colony Programmes Plus 2], [Red Book].[The Pack Essentials], [Red Book].[The Pack Programme], [Red Book].[The Pack Programme Plus], [Red Book].[The Cub Scout Powerpack], [Red Book].[The Cub Scout Membership Pack], [Red Book].[CS Badges and Award poster], [Red Book].[CS Wallchart], [Red Book].[CS Record Card], [Red Book].[CS Well Done Certificate], [Red Book].[CS Moving On Certificate], [Red Book].[CS Investiture Certificate], [Red Book].[CS Top Award Certificate (CS Silver)], [Red Book].[CS Games Book], [Red Book].[Pack Programmes Plus 2], [Red Book].[The Troop Essentials], [Red Book].[The Troop Programme], [Red Book].[The Troop Programme Plus], [Red Book].[The Scout Record Book], [Red Book].[The Scout Badge Book], [Red Book].[Scout Skills Card], [Red Book].[S Badges and Award poster], [Red Book].[S Wallchart], [Red Book].[S Record Card], [Red Book].[S Well Done Certificate], [Red Book].[S Investiture Certificate], [Red Book].[S Top Award Certificate (CS Gold)], [Red Book].[S Games Book], [Red Book].[Troop Programmes Plus 2], [Red Book].[Explorer Scouts], [Red Book].[The Unit Essentials], [Red Book].[The Unit Programme], [Red Book].[The Unit Programme Plus], [Red Book].[The Explorer Scout Membership Pack], [Red Book].[The Explorer Scout Badge Book], [Red Book].[ES Badges and Award poster], [Red Book].[ES Wallchart], [Red Book].[ES Record Card], [Red Book].[Young Leaders Certificate], [Red Book].[ES Invertiture Certificate], [Red Book].[ES Top Award Certificate (CS Platinum)], [Red Book].[Explorer Belt Certificate], [Red Book].[Explorers Programme Plus 2], [Red Book].[Young Leaders Essentials Plus], [Red Book].[Young Leaders Keys to Success], [Red Book].[Personal Record card], [Red Book].[S Handbook], [Red Book].[SN Top Award Certificate (CS Diamond)], [Red Book].[SN Investiture Certificate], [Red Book].[Scout Skills Book]
FROM [Red Book]
WHERE ((([Red Book].Beavers)=[Forms]![ReportForm]![Beavers]) AND (([Red Book].Cubs)=[Forms]![ReportForm]![Cubs]) AND (([Red Book].Scouts)=[Forms]![ReportForm]![Scouts]) AND (([Red Book].Explorers)=[Forms]![ReportForm]![Explorers]) AND (([Red Book].Network)=[Forms]![ReportForm]![Network]) AND (([Red Book].[Badges - Current])=[Forms]![ReportForm]![a]) AND (([Red Book].[Badges - New])=[Forms]![ReportForm]![b]) AND (([Red Book].Programme)=[Forms]![ReportForm]![c]) AND (([Red Book].[Certificate of Excellence])=[Forms]![ReportForm]![a62]) AND (([Red Book].[Nights Away])=[Forms]![ReportForm]![a63]) AND (([Red Book].Resources)=[Forms]![ReportForm]![d]) AND (([Red Book].[The Colony Essentials])=[Forms]![ReportForm]![a1]) AND (([Red Book].[The Colony Programme])=[Forms]![ReportForm]![a2]) AND (([Red Book].[The Colony Programme Plus])=[Forms]![ReportForm]![a3]) AND (([Red Book].[The Beaver Scout Logbook])=[Forms]![ReportForm]![a4]) AND (([Red Book].[BS Badges and Award poster])=[Forms]![ReportForm]![a5]) AND (([Red Book].[BS Wallchart])=[Forms]![ReportForm]![a6]) AND (([Red Book].[BS Record Card])=[Forms]![ReportForm]![a7]) AND (([Red Book].[BS Well Done Certificate])=[Forms]![ReportForm]![a8]) AND (([Red Book].[BS Moving On Certificate])=[Forms]![ReportForm]![a9]) AND (([Red Book].[BS Investiture Certificate])=[Forms]![ReportForm]![a10]) AND (([Red Book].[BS Top Award Certificate (CS Bronze)])=[Forms]![ReportForm]![a11]) AND (([Red Book].[BS Games Book])=[Forms]![ReportForm]![a12]) AND (([Red Book].[Colony Programmes Plus 2])=[Forms]![ReportForm]![a13]) AND (([Red Book].[The Pack Essentials])=[Forms]![ReportForm]![a14]) AND (([Red Book].[The Pack Programme])=[Forms]![ReportForm]![a15]) AND (([Red Book].[The Pack Programme Plus])=[Forms]![ReportForm]![a16]) AND (([Red Book].[The Cub Scout Powerpack])=[Forms]![ReportForm]![a17]) AND (([Red Book].[The Cub Scout Membership Pack])=[Forms]![ReportForm]![a18]) AND (([Red Book].[CS Badges and Award poster])=[Forms]![ReportForm]![a19]) AND (([Red Book].[CS Wallchart])=[Forms]![ReportForm]![a20]) AND (([Red Book].[CS Record Card])=[Forms]![ReportForm]![a21]) AND (([Red Book].[CS Well Done Certificate])=[Forms]![ReportForm]![a22]) AND (([Red Book].[CS Moving On Certificate])=[Forms]![ReportForm]![a23]) AND (([Red Book].[CS Investiture Certificate])=[Forms]![ReportForm]![a24]) AND (([Red Book].[CS Top Award Certificate (CS Silver)])=[Forms]![ReportForm]![a25]) AND (([Red Book].[CS Games Book])=[Forms]![ReportForm]![a26]) AND (([Red Book].[Pack Programmes Plus 2])=[Forms]![ReportForm]![a27]) AND (([Red Book].[The Troop Essentials])=[Forms]![ReportForm]![a28]) AND (([Red Book].[The Troop Programme])=[Forms]![ReportForm]![a29]) AND (([Red Book].[The Troop Programme Plus])=[Forms]![ReportForm]![a30]) AND (([Red Book].[The Scout Record Book])=[Forms]![ReportForm]![a31]) AND (([Red Book].[The Scout Badge Book])=[Forms]![ReportForm]![a32]) AND (([Red Book].[Scout Skills Card])=[Forms]![ReportForm]![a33]) AND (([Red Book].[S Badges and Award poster])=[Forms]![ReportForm]![a34]) AND (([Red Book].[S Wallchart])=[Forms]![ReportForm]![a35]) AND (([Red Book].[S Record Card])=[Forms]![ReportForm]![a36]) AND (([Red Book].[S Well Done Certificate])=[Forms]![ReportForm]![a38]) AND (([Red Book].[S Investiture Certificate])=[Forms]![ReportForm]![a39]) AND (([Red Book].[S Top Award Certificate (CS Gold)])=[Forms]![ReportForm]![a40]) AND (([Red Book].[S Games Book])=[Forms]![ReportForm]![a41]) AND (([Red Book].[Troop Programmes Plus 2])=[Forms]![ReportForm]![a42]) AND (([Red Book].[Explorer Scouts])=[Forms]![ReportForm]![a43]) AND (([Red Book].[The Unit Essentials])=[Forms]![ReportForm]![a44]) AND (([Red Book].[The Unit Programme])=[Forms]![ReportForm]![a45]) AND (([Red Book].[The Unit Programme Plus])=[Forms]![ReportForm]![a46]) AND (([Red Book].[The Explorer Scout Membership Pack])=[Forms]![ReportForm]![a47]) AND (([Red Book].[The Explorer Scout Badge Book])=[Forms]![ReportForm]![a48]) AND (([Red Book].[ES Badges and Award poster])=[Forms]![ReportForm]![a49]) AND (([Red Book].[ES Wallchart])=[Forms]![ReportForm]![a50]) AND (([Red Book].[ES Record Card])=[Forms]![ReportForm]![a51]) AND (([Red Book].[Young Leaders Certificate])=[Forms]![ReportForm]![a52]) AND (([Red Book].[ES Invertiture Certificate])=[Forms]![ReportForm]![a53]) AND (([Red Book].[ES Top Award Certificate (CS Platinum)])=[Forms]![ReportForm]![a54]) AND (([Red Book].[Explorer Belt Certificate])=[Forms]![ReportForm]![a58]) AND (([Red Book].[Explorers Programme Plus 2])=[Forms]![ReportForm]![a55]) AND (([Red Book].[Young Leaders Essentials Plus])=[Forms]![ReportForm]![a56]) AND (([Red Book].[Young Leaders Keys to Success])=[Forms]![ReportForm]![a57]) AND (([Red Book].[Personal Record card])=[Forms]![ReportForm]![a61]) AND (([Red Book].[S Handbook])=[Forms]![ReportForm]![a37]) AND (([Red Book].[SN Top Award Certificate (CS Diamond)])=[Forms]![ReportForm]![a59]) AND (([Red Book].[SN Investiture Certificate])=[Forms]![ReportForm]![a60]) AND (([Red Book].[Scout Skills Book])=[Forms]![ReportForm]![a64]));

[TheShabz]

omg walloftext!

[ConneXionLost]

Uh,... Yikes! I gotta agree with TheShabz on this one.

Okay Rubz. First suggestion. If you're happy that the [Red Book] table is containing everything you need, then you would be better off letting Microsoft Excel do this. You would be able to use the "Filters" feature (native to Excel) to quickly find what you've previously described.

Second suggestion. If you insist on staying with Access, then you really need to incorporate some normalization into this database. Also, dealing with this kind of information with just one form and one table will make your work very difficult; Access will NOT be your friend with this approach.

Third suggestion. The reason you aren't getting the query to give you what you want is mainly due to all the "AND" in your WHERE portion. It would be easier to only filter the attributes you are actually interested in filtering, rather than all of them. To do this you could build seperate queries for each attribute. It's also possible to build a dynamic query to select only the attributes you've selected on the form but, at 73 attributes, even that would be ugly.

Best of luck!

[Access_Blaster]

Also the word Date is a reserved word, and should not be used.

SELECT [Red Book].Ref, [Red Book].Name, [Red Book].Contact, [Red Book].Date,