Specifying no criteria in a query

  • Thread starter Thread starter Big Tony
  • Start date Start date
B

Big Tony

I am trying have a query which uses criteria only under
certain circumstances, and at other times does not.

I can get a query to specify no criteria by using the
following in the criteria box:

like "*"

I can also get it to use criteria by specifying criteria
in the box (I refer to a form for the criteria)


The problem is that when I try to combine the two into an
iif or choose statement, I get a query with no results.

Here is one of the formulas I have tried:

=Choose([Forms]![Query Control Form]![CONTROLBOX1],
Like "*",[Forms]![Query Control Form]![SITE])

Does anyone have any ideas?
 
I am trying have a query which uses criteria only under
certain circumstances, and at other times does not.

I can get a query to specify no criteria by using the
following in the criteria box:

like "*"

I can also get it to use criteria by specifying criteria
in the box (I refer to a form for the criteria)

The problem is that when I try to combine the two into an
iif or choose statement, I get a query with no results.

Here is one of the formulas I have tried:

=Choose([Forms]![Query Control Form]![CONTROLBOX1],
Like "*",[Forms]![Query Control Form]![SITE])

Does anyone have any ideas?
Click to expand...

Try:
Like IIf(IsNull(forms![Query Control
Form].[ControlBox1]),"*",forms![Query Control Form].[ControlBox1])

Enter nothing in [ControlBox1] and you'll get all records.
Enter something and only those records that match EXACTLY will be
returned.
To return records that include the entered value anywhere in the field
use wildcards:
,"*" & forms![Query Control Form].[ControlBox1] & "*")
 
Back
Top