Please help: AND OR Criteria in Query cause Criteria separation

[FrankAccess]

Hello,

I am in the middle of creating a query which pulls results from a large table based on the checkboxes that are found in a form. For instance, when I check off a checkbox called "Honda" on my form, my query will return all Table results that contain the word Honda. I made this work properly, however when I save and close the query, access splits up my criteria where I have an "AND" or an "OR" (presumably because it thinks these words are redundant, and that a column/row shift would suffice). You can what this looks like in the attached file. This doesn't create a problem in the sample database that I have created but in my actual database (over 200,000 entries), access cannot run the query once the criteria have been separated (the pre-save unseparated query version works fine).

Is there a way to prevent access from separating my query criteria? For your reference, I have included the unseparated criteria at the bottom of this post.

Thank you for your help.

PLEASE NOTE: The form called "Search" must be open in order to successfully open any of the queries. To update any query results after selecting different checkboxes, either run the query again OR close the query and reopen it. Also, I have included three queries in this database so that you can see the make and color criteria acting together and in isolation.

Criteria

Color
(([forms]![Search]![AnyColorCheck]=True) And (Is Not Null)) Or (([forms]![Search]![BlueCheck]=True) And "blue") Or (([forms]![Search]![redCheck]=True) And "red") Or (([forms]![Search]![orangeCheck]=True) And "orange") Or (([forms]![Search]![greenCheck]=True) And "green")



Make
(([forms]![Search]![AnymakeCheck]=True) And (Is Not Null)) Or ((forms![Search]![BMWCheck]=true) and "bmw") Or ((forms![Search]![mazdaCheck]=true) and "mazda") Or ((forms![Search]![porscheCheck]=true) and "porsche") Or ((forms![Search]![dodgeCheck]=true) and "dodge") Or ((forms![Search]![chevyCheck]=true) and "chevy") Or ((forms![Search]![hondaCheck]=true) and "honda") Or ((forms![Search]![mercedesCheck]=true) and "mercedes")

[June7]

If you want to allow dynamic selection of multiple criteria for one field, really need VBA code to construct filter string and apply the filter to a form or report.

Here's an example that allows selection of multiple items from a listbox http://allenbrowne.com/ser-50.html

What you have with checkboxes gets event more complicated.

[FrankAccess]

Thanks June7. I will try to use VBA [need to learn it first).

[FrankAccess]

So I tried out the VBA approach and must say that it is way over my head. Is there another way that I can do this? I don't necessarily need to use checkboxes, a multi-select list box will work as well. I have looked at other threads that explained how to use a multi-select list box but was unable to get this to work for me. Would someone be kind enough to create a query that gets its parameters from a multi-select listbox?

[June7]

The VBA example demonstrated in that link is the only way. If you Google 'access vba multi-select listbox filter' most hits will lead you back to Allen Browne's code or something along the same lines. Here is a thread discussing the issue and has a db attached you might find helpful http://www.access-programmers.co.uk/...d.php?t=215560