Blank Combo Box Filters

[Matt]

I have two combo boxes on a form to select Region and Customer. If the
Region is selected (e.g. North America), but a customer is not, I want all
the records to come back for that region regardless of customer.

If the customer is selected (e.g. Dell), but a region is not, I want all the
records to come back for the customer selected regardless of region.

And then if a Region and Customer is selected, I want it to display the
records that match both selections.

How would I go about doing this? Do I need to write code to make this
happen? Thanks in advance for your help.

The table I'm referencing has a primary key on Product, Region, and
Customer.

 

[KARL DEWEY]

Try this --
SELECT Product, Region, Customer
FROM YourTable
WHERE ([Region] = [Forms]![YourFormName]![cboRegion] OR
[Forms]![YourFormName]![cboRegion] Is Null) AND ([Customer] =
[Forms]![YourFormName]![cboCustomer] OR [Forms]![YourFormName]![cboCustomer]
Is Null);

 

[Matt]

Hi Karl,

Thanks for the help. It works! But the problem I run into is when I try to
select another region or customer from the drop down box, the results don't
come up. I just get blank records. I have to close the form and then
re-open it before the correct results will come up.

How do I fix that?

Thanks,
Matt

Try this --
SELECT Product, Region, Customer
FROM YourTable
WHERE ([Region] = [Forms]![YourFormName]![cboRegion] OR
[Forms]![YourFormName]![cboRegion] Is Null) AND ([Customer] =
[Forms]![YourFormName]![cboCustomer] OR [Forms]![YourFormName]![cboCustomer]
Is Null);

--
Build a little, test a little.

I have two combo boxes on a form to select Region and Customer. If the
Region is selected (e.g. North America), but a customer is not, I want all
the records to come back for that region regardless of customer.

If the customer is selected (e.g. Dell), but a region is not, I want all the
records to come back for the customer selected regardless of region.

And then if a Region and Customer is selected, I want it to display the
records that match both selections.

How would I go about doing this? Do I need to write code to make this
happen? Thanks in advance for your help.

The table I'm referencing has a primary key on Product, Region, and
Customer.