Ignore combo box if null in parameter query

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

I have a form that allows the user to select up to 3 products in a
side by side comparison. This is done with 3 combo boxes. Once the
user has made his selections, he is to click on a command button to
see the results of his selections. Everything works fine as long as 3
products are selected. But, if the user only wants to compare 2
products, zero records are returned. How can I have the query ignore
the third combo box if no selection is made?

My method of passing the combo box selections to the queries is by
inserting [forms]![formname]![controlname] in the criteria section of
the query. Thanks for any suggestions.
 
Dear Dale:

I expect the user could leave any of the 3 combo boxes "unselected".

How about:

WHERE (Product = Forms!YourFormName!ComboBox1 OR
Forms!YourFormName!ComboBox1 IS NULL)
AND (Product = Forms!YourFormName!ComboBox2 OR
Forms!YourFormName!ComboBox2 IS NULL)
AND (Product = Forms!YourFormName!ComboBox3 OR
Forms!YourFormName!ComboBox3 IS NULL)

I'm not sure just what all the rules are, but I think just MAYBE the
combo boxes could be blank, rather than NULL. It looks the same, but
doesn't test the same. So, what I'd really use would be:

WHERE (Product = Forms!YourFormName!ComboBox1 OR
Nz(Forms!YourFormName!ComboBox1, "") = "")
AND (Product = Forms!YourFormName!ComboBox2 OR
Nz(Forms!YourFormName!ComboBox2, "") = "")
AND (Product = Forms!YourFormName!ComboBox3 OR
Nz(Forms!YourFormName!ComboBox3, "") = "")

If I'm wrong about the possibility of the combo box being blank as
well as NULL, well it will still work just fine. But if I'm right,
well this is safer.

I'm thinking that if you start to enter something in the combo box,
then backspace it out, it might be blank not null. Not sure in what
cases this happens, but I really do believe I've seen it happen.
Anyway, better safe than sorry!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I have a form that allows the user to select up to 3 products in a
side by side comparison. This is done with 3 combo boxes. Once the
user has made his selections, he is to click on a command button to
see the results of his selections. Everything works fine as long as 3
products are selected. But, if the user only wants to compare 2
products, zero records are returned. How can I have the query ignore
the third combo box if no selection is made?

My method of passing the combo box selections to the queries is by
inserting [forms]![formname]![controlname] in the criteria section of
the query. Thanks for any suggestions.
Click to expand...

Please ignore my question. I figured out what I was doing wrong. Dumb question.
 
Back
Top