Querying off of a combo box

[jiejie]

I have a combo box that lists out, say, 4 option values, and a check box beside it to enable/disable the combo box. Then I have a query run on a table based on the option chosen in the combo box. However, if the check box is not clicked , i.e., the combo box is not enabled in the first place, then I want the query to return the complete set of data, i.e., with all 4 options included. I used the "iif" function to say "iif(check1=-1, [ComboBox1], xxxx)", what should that xxxx be? I tried all kinds of things such as "IsNotNull" but nothing worked. Any ideas?

 

[cghoga]

try using the * wildcard.

=like iif(Forms!NameofYourForm!Check1.value=-1,"*",Forms!NameofYourForm!combobox1)

the -1 may need to be a 0.

hope this helps you.

 

[jiejie]

Hi! I tried that, too. It didn't work. I think it's looking for anything that contains the character "*" literally.

 

[Eupher]

Where are you calling the query? A command button? An After Update event? Can you post some more of the code so we can get a little better sense of the context?

Ken S.

 

[jiejie]

Yes, I'm calling the query via a command button. A macro that opens the query is attached to the OnClick event of the command button. The macro is very simple - open the query, open a report sourcing from the query, and then close the query. As far as the query itself, I built the criteria on a field based on selection on the combo box.

 

[Eupher]

I don't use macros, I use code for everything. I think the simplest approach might be to have 2 saved queries: one to return the entire table, the other to return a subset based on the selection in the combo. It sounds like you already have the 2nd query built, i.e. you have [Forms]![myFormName]![myComboBox] as the criteria for one of the fields? If so, simply copy and paste the query, naming the copy as appropriate, and remove the criteria from it so it returns the entire table. Then create an event procedure in the On Click event of your command button with code that looks something like this:

If Me![check1] = True Then
     DoCmd.OpenQuery "qryQuery1"          'based on value in combo
     Else
          DoCmd.OpenQuery "qryQuery2"     'all records
End If

HTH...

Ken S.

 

[msiqbal]

I am having a problem with combo/list box. I want to be able to capture values selected and also inputted in a text box to run a parameter query.
How can I take the the values from the list box and text box and input them for the two parameter values required in the query?
Thanks.