Combo box selection - criteria for query

  • Thread starter Thread starter PC
  • Start date Start date
P

PC

I have a query on which a report is based. The criteria for the query is
selected from a Combo Box on a form and a command botton on the form is used
to run the report. How would I specify that if the selection in the combo
box is blank that all records in the criteria column are selected and
visible in the report.

The code for the query is a follows:

SELECT Table.[Comapny Name], Table.Address, Table.Phone, Table.Category
FROM Table
WHERE (((Table.Category)=[Forms]![Form3]![Combo0]));

TIA

pc..
 
Dear PC:

SELECT [Comapny Name], Address, Phone, Category
FROM Table
WHERE Category =
IIf(Nz([Forms]![Form3]![Combo0], "") = "", Category,
[Forms]![Form3]![Combo0]);

This says that, if the combo box is NULL or empty, then filter by
Category = Category which will always be true. Thus, no value showing
in the combo box means do not filter.

I have a query on which a report is based. The criteria for the query is
selected from a Combo Box on a form and a command botton on the form is used
to run the report. How would I specify that if the selection in the combo
box is blank that all records in the criteria column are selected and
visible in the report.

The code for the query is a follows:

SELECT Table.[Comapny Name], Table.Address, Table.Phone, Table.Category
FROM Table
WHERE (((Table.Category)=[Forms]![Form3]![Combo0]));

TIA

pc..
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
You can do one of the two things.

1) Create two queries and decide which one to use.

sub whichone ()
if isnull(me![combofield]) then
docmd.runquery "AllQuery"
else
docmd.runquery "Query1"
end if
end sub

AllQuery: select fields, ... from tables;

Query1: select fields, ... from tables where fields =
formfields;

2) Using "like", which will return everyting that
partially match your criteria.

SELECT fields, ....
FROM Tables
WHERE Table.Category like iif(isnull([Forms]![Form3]!
[Combo0]), "*", [Forms]![Form3]![Combo0] & "*");
 
Back
Top