Looking for Mere Clever Way to Write Query

M

Mike Thomas

Using the Access query builder and tying the WHERE clause to a combo box on
a form, the user selects records to include by making a selection in the
combo box. The WHERE clause look like this:

WHERE ((company.CompanyID)=[Forms]![InventoryReport]![cboCompany]));

What I woudl like to do is allow the user to leave the combo box blank to
include records for all companies in the result set. I can do it in the
query builder with an "OR" clause and something like CompanyID >
IIF(isnull([Forms]![InventoryReport]![cboCompany])), 0, 2000000), but I am
wondering if someone has a better way to do this.

Many thanks,
Mike Thomas
 
S

Stefan Hoffmann

hi Mike,

Mike said:
Using the Access query builder and tying the WHERE clause to a combo box on
a form, the user selects records to include by making a selection in the
combo box. The WHERE clause look like this:

WHERE ((company.CompanyID)=[Forms]![InventoryReport]![cboCompany]));
Click to expand...
OR NOT IsNull([Forms]![InventoryReport]![cboCompany])

Should leave the recordset blank, when nothing is selected.

mfG
--> stefan <--
 
M

mscertified

You could set up a second query for when the selection field is blank then do
an if then...else to determine which query to use. It will be more efficient
that doing the OR test in a single query.

Dorian.
 
Top