Can a query ignore criteria when the criteria is blank?

[Guest]

I have reached a stumbling block and I was hoping to someone could help me
out. I have an unbound form where a user can enter information into multiple
un bound text boxes. I then use these boxes as the criteria for a query.
The query in turn is then used to produce a report and a seperate form. The
issue I have is that there are several different text boxes where a user can
enter information. I have set most of these up to function as "Or" criteria.
However, I need to add and "And" criteria. Now this setup works as long as
I use the "And" criteria text box in conjunction with any of the "Or"s. Is
there anyway to tell the query to ignore this "And" when the value is Null.
I think I could use a nested IIF but I am not really sure how to get started.
Any ideas?

I know that was a confusing description, I hope this helps:

A user can create a report by typing in one or more cost centers, store
names, regions, or scores as long as the market is selected. I want them to
be able to use any criteria on the form independently, but if there is a
market selected I want the report to be limited to cost centers, regions,
store name, or scores in that market.

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Although the example in the article sets the Filter for a form, the code to
create the WhereCondition for OpenReport is identical.

As you found, it quickly becomes a nightmare to provide lots of optional
parameters of varying data types and get it work correctly. It is much more
efficient and reliable to create the filter string programmatically from
only the boxes where the user entered something.

BTW, there is another issue if you are mixing ANDs and ORs. You need to be
aware that:
(a AND b) OR c
gives different results than:
a AND (b OR c)
where a, b, and c are expressions.

 

[Sophie]

How about modifying your query by adding an expression field IIF statement
where the NULL result to a criteria =1 for AND whereas criteria=0 (zero) for
OR conditions.

Expr1:IIf(function_result IS NULL,1,0)
1
0

it's kinda hard to explain, it's easier in the query design mode. Anyway, I
hope it's clear.

Jordan