query too complex to pass optional parameters

[inungh]

I have a query to pass 9 optional parameters like following

[forms]![myform]![mycontrols] or [forms]![myform]![mycontrols] is null

There are 9 paramters like above.
MS Access creates a query with more than 128 lines in SQL design. I
got query is too complex when I execute the query.


I just wanted to know are there any limitations to pass optional
parameters to database for MS Access? If there is, what are the
limitations? are there any workaround to pass more than 8 optional
parameters to database using query?

Your information is great appreciated,

 

[Michel Walsh]

I suspect that the 'or' operator is the killer, not the number of
parameters in itself.


Change (in SQL view)


WHERE ... AND (fieldName = [forms]![myform]![mycontrols] OR
[forms]![myform]![mycontrols] is null ) AND ...

to

WHERE ... AND iif( [forms]![myform]![mycontrols] is null, true, field =
fieldName [forms]![myform]![mycontrols] ) AND ...


which eliminates the OR operator.



It may be preferable to write an ad hoc query: the previous query cannot be
as optimized as a query involving only directly the fields that have to be
'filtered': the OR construction leads to complex plan, the iif construction
leads to plan not taking into account the indexes of the fields (if there is
any).



Vanderghast, Access MVP