Is Not Nulls and Comboboxes

[assassin4213]

Hello Everyone,

Here's the scoop. I have a query that sorts data that is referencing an unbound combobox. I have two scenarios.

1. When a vendor is selected in the combobox - I would like the query to sort the data set and show only that vendors data samples.

2. When no vendor is selected in the combobox - I would like the query to default to showing all the vendors in the data set.

I have tried writing a formula in the query statement for the vendor segment. Here's the concept formula that I wrote in the criteria area for strVendor portion of the query.

IIf((IsNull([Forms]![Vendor Fuel Customized Selection]![cboVendor])),(([qryFuel2001].[strVendor]) Is Not Null),[Forms]![Vendor Fuel Customized Selection]![cboVendor])

I tried using an Is Not Null statement since this works on selecting vendor only samples on other queries. However, I can't seem to get it working in this formula. Would anyone have any recommendations on how to get the Is Not Null to work or any other alternatives?

Talk to ya' later,

Greg

 

[assassin4213]

Solution Work Around

I was opening the query in a subform. Well, I found a work around by making two queries that handled the comboboxes differently. With an if/then statement and the isnull function I was able to have the correct query set as the sourceobject for the subform.

 

[Mile-O]

Workarounds are not good; they indicate flaws...somewhere.

Return to your original query and set the criteria (changing names, obviuosly) to:

Forms!MyForm!MyCombo OR Forms!MyForm!MyCombo Is Null

all as one line. When you save the query and close it and then go back into it you'll see that the structure has changed sightly but it should do what you originally wanted.