Using flexible query criteria

[keliie]

Hello

Just a quick question that I've spent a few hours trying to solve with
no luck (although one would think this should be fairly easy).

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've tried an "IIF" statement in the query criteria with no luck, also
tried (my best) to write VB code to do the same, again without any
luck.

I'm sure there is a simple solution, but I have yet to find it. Any
help at all would be appreciated.

Best,

Kelii

[Gabriele Bertolucci]

> 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

[Ed Robichaud]

Open your underlying query in design-grid view and something like below:

Like IIf([Forms]![frmMyForm]![pickCombBox] Is
Null,"*",[forms]![frmMyForm]![pickCombBox])

as the criteria in the column of your main form combobox.
-Ed

<ke****@yahoo.com> wrote in message
news:11*********************@j52g2000cwj.googlegro ups.com...

Hello

Just a quick question that I've spent a few hours trying to solve with
no luck (although one would think this should be fairly easy).

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've tried an "IIF" statement in the query criteria with no luck, also
tried (my best) to write VB code to do the same, again without any
luck.

I'm sure there is a simple solution, but I have yet to find it. Any
help at all would be appreciated.

Best,

Kelii

[keliie]

Ed - thanks for the response, it didn't seem to work.

This is the text that I put into the query criteria:
Like IIf([Forms]![frmInvoiceLookup]![Vendor_Name] Is
Null,"*",[Forms]![frmInvoiceLookup]![Vendor_Name])

Here is the full query:
SELECT tblInvoiceMaster.Invoice_ID, tblInvoiceMaster.Company_Location,
tblInvoiceMaster.Invoice_Date, tblInvoiceMaster.Invoice_Number,
tblInvoiceMaster.Vendor_Name, tblInvoiceMaster.Total_Invoice_Amount
FROM tblInvoiceMaster GROUP BY tblInvoiceMaster.Invoice_ID,
tblInvoiceMaster.Company_Location, tblInvoiceMaster.Invoice_Date,
tblInvoiceMaster.Invoice_Number, tblInvoiceMaster.Vendor_Name,
tblInvoiceMaster.Total_Invoice_Amount HAVING
(((tblInvoiceMaster.Company_Location)=[Forms]![frmInvoiceLookup]![Company_Location])
AND
((tblInvoiceMaster.Invoice_Date)>=[Forms]![frmInvoiceLookup]![Invoice_Begin_Date]
And
(tblInvoiceMaster.Invoice_Date)<=[Forms]![frmInvoiceLookup]![Invoice_End_Date])
AND ((tblInvoiceMaster.Vendor_Name) Like
IIf([Forms]![frmInvoiceLookup]![Vendor_Name] Is
Null,"*",[Forms]![frmInvoiceLookup]![Vendor_Name])));

Any other thoughts?

Best,

Kelii

[inkman04]

Hello Kel,

With the risk of sounding redundant, the expression below
is basically what I use to deal with the problem your having
and works for me everytime.

Perhaps you can give it a go! I use the build button to get
the correct path I need to the control on the main form for
the field in the query.

Result:

Forms![MainFormName]![FieldNameOnForm] 0r
Forms![MainFormName]![FieldNameOnForm] Is Null

Just get the path to the control you need and then repeat
it again after an "or" with the "Is Null" at the end.

This way, it looks to see if there is data in the field first,
then if it is null, you get all records.

Regards

[Ed Robichaud]

It looks OK; try deleting all the other criteria from your query and test
if the response works then. This assume that ALL records have value in
[Vendor_Name]; if not you'll have to add "or is Null" to that particular
query criteria.
-Ed
<ke****@yahoo.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

Ed - thanks for the response, it didn't seem to work.

This is the text that I put into the query criteria:
Like IIf([Forms]![frmInvoiceLookup]![Vendor_Name] Is
Null,"*",[Forms]![frmInvoiceLookup]![Vendor_Name])

Here is the full query:
SELECT tblInvoiceMaster.Invoice_ID, tblInvoiceMaster.Company_Location,
tblInvoiceMaster.Invoice_Date, tblInvoiceMaster.Invoice_Number,
tblInvoiceMaster.Vendor_Name, tblInvoiceMaster.Total_Invoice_Amount
FROM tblInvoiceMaster GROUP BY tblInvoiceMaster.Invoice_ID,
tblInvoiceMaster.Company_Location, tblInvoiceMaster.Invoice_Date,
tblInvoiceMaster.Invoice_Number, tblInvoiceMaster.Vendor_Name,
tblInvoiceMaster.Total_Invoice_Amount HAVING
(((tblInvoiceMaster.Company_Location)=[Forms]![frmInvoiceLookup]![Company_Location])
AND
((tblInvoiceMaster.Invoice_Date)>=[Forms]![frmInvoiceLookup]![Invoice_Begin_Date]
And
(tblInvoiceMaster.Invoice_Date)<=[Forms]![frmInvoiceLookup]![Invoice_End_Date])
AND ((tblInvoiceMaster.Vendor_Name) Like
IIf([Forms]![frmInvoiceLookup]![Vendor_Name] Is
Null,"*",[Forms]![frmInvoiceLookup]![Vendor_Name])));

Any other thoughts?

Best,

Kelii

[keliie]

Inkman,

Thanks for the response, your suggestion works for 99% of the issue,
which I'm willing to live with. The only complication is that the "Is
Null" criteria seems to prevent the other query criteria from
functioning properly; e.g., the date criteria have no effect when I
leave the Vendor_Name criteria blank. I've repeated the date query
criteria in the second row of the query criteria along with the "Is
Null" for Vendor_Name, but still no luck.

I'll keep playing with it to see if it works, however, great
suggestions and thanks for the help.

Best,

Kelii

[keliie]

Inkman,

Scratch earlier comment, your suggestion works perfectly.

Thanks again,

Kelii