Re: Using flexible query criteria

I have a form with a subform. The subform is based on a query. The
criteria for the query is based on the user's input in the main form.
One of the user inputs is a field called "Vendor_Name" (in the form of
a combo box). I would like the query to run for all "Vendor_Name" if
the user leaves the field blank. However, if the user selects a
"Vendor_Name", then I would like the query to run with the selected
criteria.

I suggest you to change the SELECT used for RowSource of the combo in the main form.

Said the SELECT statement is similar to

SELECT Filed1, Field2, ...., FieldN FROM MyTable

and said that Field1 is the primary key and is an autonumber field, and Field2 is the field shown in the combo box, you must modify it to a UNION select, similar to

SELECT Field1, Field2, ...., FieldN FROM MyTable
UNION 0 AS Field1, '<all>' AS Field2, NULL AS Field3, ...., NULL AS FieldN FROM MyTable

then, you should change the SELECT used for RecordSource of the subform.

Said this SELECT is similar to

SELECT Field1, Field2, ...., FieldX FROM MyTable

you must modify it in a way similar to

SELECT Field1, Field2, ...., FieldX FROM MyTable
WHERE (Field1 = [Forms]![nameofthemainform]![nameofthecombobox]) OR
([Forms]![nameofthemainform]![nameofthecombobox] = 0)

Bye.

--
PBsoft di Gabriele Bertolucci
www.pbsoft.it
skype: pbsoftsolution


.