Query criteria

[Guest]

Hi, I have a query that has a field customer. In the criteria cell I have a
link to a combobox in a form that has all the customers name. And in that
form using a subform I am bringing up the result of the query. As a result it
is showing all the entries for that customer. Everything is working up to
now.
In the combobox I also would like to add a text like ALL so that when the
user chose it, it shows all the customers.
* alone is working in the query, but when I have * in the combobox linked to
the query it does not work. Can anyone help...

thanks

 

[Allen Browne]

You can change the criteria of your query so that if the control on the form
is left blank, all records are returned.

To do that, switch the query to SQL View.
Locate the WHERE clause.
Replace:
(Table1.CustomerID) = [Forms].[Form1].[CustID]
with:
(([Forms].[Form1].[CustID] Is Null) OR
(Table1.CustomerID = [Forms].[Form1].[CustID]))

If the CustID control is left blank (null), the first part of that
expression returns True, so all records get returned. If the control is not
blank, then the 2nd part is evaluated, i.e. only matching records are
returned.