Looking for Mere Clever Way to Write Query

[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

 

[Stefan Hoffmann]

hi Mike,

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]));

OR NOT IsNull([Forms]![InventoryReport]![cboCompany])

Should leave the recordset blank, when nothing is selected.

mfG
--> stefan <--

 

[Guest]

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.

 

[Stefan Hoffmann]

hi,

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

OR IsNull([Forms]![InventoryReport]![cboCompany])

Gives you all records when your combo is null.


mfG
--> stefan <--