Open Form with multiple selected values from combo box

[5694]

I created a form (FORM A) with 5 combo box for users to select values. Then I created a query that uses the values on the FORM A as criteria.
I placed a Search Button on FORM A to open a result form called FORM B with that values.

The query is not returning the results I want. If I use the OR logic on the query (criteria on combo box one, or two or three…), it will return large number of results with that criteria in that field.
If I use AND logic user have to select all 5 criteria and they will get one answer (as it should). But they don’t usually know the 5 values at most they’ll know 2 or 3.

What I want is if they chose 3 criteria, I want the result to be the union of the 3 selected fields and the 2 nulls. This way it will return at least 100 records instead of 800.

Please help

 

[PHV]

While not just build your WHERE clause (or perhaps Filter property) ANDing only the 2 or 3 non empty values ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[5694]

Thanks for your help. I don’t know which fields will be empty at a given search. Users can choose any combination of the 5-combo box.

 

[PHV]

And in VBA you can't check the 5-combo box ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884