Complex Combo Box Query

[jpmillr1]

I want to do a query based on combo box values, with the functionality that when all boxes are left blank, all records will be selected (because no criteria was specified via the combo boxes).

The query returns records containing data that matches the combo boxes, and if combo boxes are left blank, those criteria do not matter.

I can do queries based on single combo boxes -- how do I expand this to multiple boxes?

Thanks!
John

 

[pdbowling]

Hmm, maybe try this. I did something similar but the code was not pretty.

If the number of fields is not prohibitive, I had the drop down boxes and a button to launch the query.

I then had several if statements checking the comboboxes to see if they were empty and ran a different query depending on the combos.

something like this with ..say ,,, four criteria

if combo1 = "" and combo2 = "" and combo3 = "" and combo4 = "" then
mySQL = "Select * from table"
DoCmd.RunSQL (mySQL)'or whatever you choose to run

elseif combo1 = "" and combo2 = "" and combo3 = "" then
mySQL = "Select * from table where columnX = '" & combo4.text & "'"

elseif combo1 = "" and combo2 = "" and combo4 = "" then
mySQL = "Select * from table where columnY = '" & combo3.text & "'"
...
...
end if

and so on for each permutation of combo boxes. Of course the mySQL will be reflective of your intended query.

If you have too many permutations to deal with, I don't know what to say except, restrict the choices in some way where you won't have to write so many queries. If this solution is total crap, maybe repost so that you will still have zero responses on your post and get more readers.

Good luck.
PB

 

[jpmillr1]

That explanation would have the functionality I am looking for, but with 6 combo boxes there are many permutations...so yes, I guess I am just seeking a 'shortcut' if one exists.

Thanks though!
John