Using A Combo Box Selection For Query Criteria

[jereece]

I have a report that is based on a query. In the query, I have a field called "Mode". In the criteria section for the Mode field, I am calling the selection from a combo box on a form called "frm_main". So the criteria for the Mode field is "[Forms]![frm_Main]![Mode_ComboBox]". This combo box has selections for 1,2,3,4,5. I want a selecton on the combo box that will work with the query to show all modes. Sometimes something weird gets entered into the database like "NoMode" for example. I have tried adding a combo box selection of "*" , "Is Not Null" and "". If I manually type these into the criteria section of the query, it works fine. But when I use these in the combo box and call the combo box selection from the query, it does not work.

Does anyone have any idea what I can use in my combo box selection that when selected, the query (and hense report) will show all data?

Thanks,
Jim

 

[gemma-the-husky]

if you want exceptions, try not in (1..5) as a parameter

otherwise, something I do quite often, make the combo box query a union with another row to return ALL, something like columns -1, "<ALL>", etc. The "<ALL>" will sort it at the top of the list. Then you need an extra column in your query to test whether the combo box value was -1, and an extra row to test the query results.

 

[jereece]

Using Not won't work becasue that would exclude 1-5 and in the All I want to see them in addition to the other stuff. I am just confused why * will work in a query criteria field but not when called from a combo box.

Jim

 

[gemma-the-husky]

I thought the not test would let you examine the exceptions separately

------------------------------------------------------
i suppose it depends how you structure the query. If you calling with sql (or setting query criteria, it looks slightly different) with a test such as

"[criteriafield] = " & mycombobox

then to get everything, you either want no test at all, or presumably

"[criteriafield] like "*",

but not
"[criteriafield] = "*"

so the syntax has to change depending on the combobox test.

Hopefully i've got the SQL about right.

 

[jereece]

The criteria field is [Forms]![frm_Main]![Mode_ComboBox]. So the critera looks to see what is in the combo box. I can add Like "*" to the combo box and it will not work. However add this directly to the critera field manually (instead of the code to look for what is in the combo box) and it works fine.

Also, I am not sure how to have no test at all in a combo box.

I am really confused.

Jim

 

[jereece]

Just reposting in hopes that someone can help.