optional parameter for query

[inungh]

I have a query and would like to have optional parameters for the
users.
Because I have more than 10 optional paramters which MS Access gives
me query too complex and does not run it.

I wonder that are there any easier way to have more than 10 optional
parameters for users.


I use "[forms]![MyForm]![MyDropDown] or [forms]![MyForm]![MyDropDown]
is null" in criteria field.

MS Access creates a very long SQL. I think it is why MS Access
complained the SQL too complex.


any information is great appreciated,

 

[KARL DEWEY]

Try
Like IIF(IsNull([forms]![MyForm]![MyDropDown]), "*",
[forms]![MyForm]![MyDropDown])

or Like Nz([forms]![MyForm]![MyDropDown], "*")

 

[Marshall Barton]

I have a query and would like to have optional parameters for the
users.
Because I have more than 10 optional paramters which MS Access gives
me query too complex and does not run it.

I wonder that are there any easier way to have more than 10 optional
parameters for users.

I use "[forms]![MyForm]![MyDropDown] or [forms]![MyForm]![MyDropDown]
is null" in criteria field.

MS Access creates a very long SQL. I think it is why MS Access
complained the SQL too complex.

Aren't you getting tired of popup prompts constantly asking
you to enter something (or nothing).

A much nicer user interface for both users and your query is
to use a form where user can fill in the values they want to
use and then click on a button to do whatever the query is
supposed to accomplish.

The standard approach is exemplfied by
http://allenbrowne.com/ser-62.html

This way, the criteria that is not used does not appear in
the query so the query is greatly simplified. If you feel
the code in Allen's example is over your head, I think
you'll find that learning how to do it is easier than
dealing with "query too complex" errors. And you end up
with much nicer user interactions too.

 

[inungh]

I have a query and would like to have optional parameters for the
users.
Because I have more than 10 optional paramters which MS Access gives
me query too complex and does not run it.

I wonder that are there any easier way to have more than 10 optional
parameters for users.

I use "[forms]![MyForm]![MyDropDown] or [forms]![MyForm]![MyDropDown]
is null"  in criteria field.

MS Access creates a very long SQL. I think it is why MS Access
complained the SQL too complex.

Aren't you getting tired of popup prompts constantly asking
you to enter something (or nothing).

A much nicer user interface for both users and your query is
to use a form where user can fill in the values they want to
use and then click on a button to do whatever the query is
supposed to accomplish.

The standard approach is exemplfied byhttp://allenbrowne.com/ser-62.html

This way, the criteria that is not used does not appear in
the query so the query is greatly simplified.  If you feel
the code in Allen's example is over your head, I think
you'll find that learning how to do it is easier than
dealing with "query too complex" errors.  And you end up
with much nicer user interactions too.

Thanks millions,