Filter records based on multiple checkboxes

[kbremner]

My problem is I want to use a large number of checkboxes and in "groups" to filter the results. There will be a number of different groups of criteria.

For example I would have checkboxes for "Commercial" and "Residential" and boxes for "Income Level 1", "Income Level 2" and "Income Level 3" and probably more. By default, all of these boxes would be checked and all of the records would be displayed on the (sub)form. I want a user to be able to (either on the fly, or make all the changes and then press a command button) uncheck boxes and have the appropriate data displayed. I originally had just one set of checkboxes - commercial and residential. In the After_Update event for each checkbox, I basically checked the other. In other words, you uncheck Commercial, Access would check to see if residential was checked and then show records based on that. That method will get very cumbersome as I add new options. Is there an easier way? Please point me in the right direction, as to where I might be able to get some insight into this type of thing.

[ajetrumpet]

there is no easy way to do this. filter a query by a form. the more criteria content there is, the more you have to write, plain and simple.

The first thing you have to do is figure out if the multiple criteria possibilities are going to be using AND or OR. That'll be the toughest of any of it. More than one becomes confusing, and you tumble down the hill from there.

I doubt you can write code to simplify it too, because you can't write dynamic sql statements in the query builder. In code you can, but that's a different story.

By far, it would be much less time consuming (if you could write the code without researching) to write a small procedure behind the form that loops the controls and creates a querydef based on your values, then opens it.

By the way, are you perhaps related to Rory Bremner?? I remember him from the earliest epidoes of "Whos Line is it Anyway?" in London. Great show...

[kbremner]

First, no I am not related...

Second, your answer is incorrect. It should have begun with "The easy way to do this is......"

All joking aside, I suspected that it would be a coding nightmare, but was hoping there was a simpler way that I had not thought of. Knowing this, however, tells me that I need to be very meticulous in planning my "filters" so I don't need to add or change them later...one headache!