Too Complex Query

[Eli the Iceman]

I have a query that interacts with a form to allow users to create the
criteria. Basically, it's a form with drop down controls and a fixed text of
ALL as an option. When a user opens the form, they can select what elelments
to want to pull or leave with ALL for all records. Basicaly, it's a filter.
The query looks at the form and filters the records as needed. I call the
form field in the criteria and do OR ="ALL". Works great, but sometimes the
query crashes with Query TOO Complex and will not run again until I rebuid
it. In design mode I can see it, but sometimes it caused Access 2007 to
crash. Is there any other way to create a form that will allow users to
select desired fields that they would like to filter the records by?

Thanks,

 

[Allen Browne]

Take a look at this example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

It explains how to build a filter string from only the boxes where the user
entered something. It may take you half an hour to work through the example
and build something similar if you are not familiar with it, but there are
many situations where you can use that knowledge (filtering both forms and
reports.)

The issue with "ALL" is that this forces the combo to be a Text data type.
When you then apply that against a field of a different data type (Number,
Date, ...) JET messes up. Additionally, you can end up with too many ANDs or
ORs in the query. And that's without describing the convoluted query string
you have to maintain and the inefficiency of trying to execute it.

The example above avoids all of that. The user just leaves the box blank if
they don't want to filter on that field, and the query engine (JET) doesn't
apply any filter on that field.