Query using "IN"

[btrini10]

Hello,

I have four radio buttons on a form that allows a user to select any combination of the 4. I have an unbound field "MREALL" that is holding the values of the four radio buttons. I am then trying to filter a query depending on the combination of radio buttons selected by the user.
I am able to build the query criteria where one radio button is selected but having difficulty where multiple buttons are selected.

My code is below. I suspect I need to add an "OR" or use "IN" in the "Then" part of the criteria.

Like IIf([Forms]![frm_Main_Menu]![MREALL]="-1-1-1-1","*") Or IIf([Forms]![frm_Main_Menu]![MREALL]="-1-1-1-1","None") Or IIf([Forms]![frm_Main_Menu]![MREALL]="000-1","None") Or IIf([Forms]![frm_Main_Menu]![MREALL]="0000","Empty") Or IIf([Forms]![frm_Main_Menu]![MREALL]="-1000","Strong") Or IIf([Forms]![frm_Main_Menu]![MREALL]="0-100","Adequate") Or IIf([Forms]![frm_Main_Menu]![MREALL]="00-10","Weak") Or [highlight #FCE94F]IIf([Forms]![frm_Main_Menu]![MREALL]="00-1-1",[highlight #FCE94F]In ("Weak","None"))[/highlight]
[/highlight]
Thanks for the help.

 

[SkipVought]

IIF([Forms]![frm_Main_Menu]![MREALL]="00-1-1",[Forms]![frm_Main_Menu]![MREALL] In ("Weak","None"))

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

I have four radio buttons on a form that allows a user to select any combination of the 4" - I think this is a False statement.
Radio buttons, also called option buttons, allow you to chose one and only one out of (in your case) 4 buttons. What you describe is the behavior of check boxes, when you can have none, 1, 2, 3, or all 4 selected.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[btrini10]

Thank you for the response guys.
Andy, I have four separate radio/option button controls not checkboxes. And I may be wrong, but I don't think the control I use would make much of a difference. I would still need to figure out the logic to account for all the user options.

Hey Skip, thanks for the response. your response would work but the criteria field I am using is calculated because there were null values and I was having difficult handling them.

Below is the SQL if I use the original field and the "Is Null" does not work. Can you assist me in correcting the syntax? If I get the Is Null to work then I can use your suggestion.

SELECT DISTINCT tbl_CRAS_MRE.[Business Area], tbl_CRAS_MRE.[MR Name], tbl_CRAS_MRE.[Source Short Name], tbl_CRAS_MRE.[MR Control Effectiveness Rating], IIf([MR Control Effectiveness Rating] Is Null,"None",[MR Control Effectiveness Rating]) AS Rating
FROM tbl_CRAS_MRE
WHERE (((tbl_CRAS_MRE.[MR Control Effectiveness Rating]) Like IIf([Forms]![frm_Main_Menu]![MREALL]="-1-1-1-1","*"))) OR [highlight #FCE94F](((tbl_CRAS_MRE.[MR Control Effectiveness Rating])=IIf([Forms]![frm_Main_Menu]![MREALL]="-1-1-1-1",(tbl_CRAS_MRE.[MR Control Effectiveness Rating]) Is Null)));[/highlight]

Thanks

 

[Andrzejek]

Without going to your WHERE part, wouldn't it be better / easier to have:
[tt]
SELECT DISTINCT
tbl_CRAS_MRE.[Business Area],
tbl_CRAS_MRE.[MR Name],
tbl_CRAS_MRE.[Source Short Name],
tbl_CRAS_MRE.[MR Control Effectiveness Rating], [red]
IIf([MR Control Effectiveness Rating] Is Null,"None",[MR Control Effectiveness Rating]) AS Rating[/red]
FROM tbl_CRAS_MRE
...[/tt]

(you have 2 Rating columns :-( )
to be
[tt]
SELECT DISTINCT
[Business Area],
[MR Name],
[Source Short Name], [blue]
NZ([MR Control Effectiveness Rating], "None") AS Rating[/blue]
FROM tbl_CRAS_MRE
...[/tt]

(1 Rating column )
???

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[btrini10]

Hey Andy,

to be honest the only reason I created the "Rating" field was because I was having difficulty creating a query to filter the Null values in "MR Control Effectiveness Rating". The "Rating" field replaced the Null with None.

I would prefer to not create the "Rating" field and instead have my queries be able to filter on the Null in the "MR Control Effectiveness Rating" field.

With the below where clause I am trying to get all records on the table even if there is a Null value. The first part where I am using a "Like" works, but the second part where I am looking for the Nulls does not work.

I hope that makes sense.

WHERE (((tbl_CRAS_MRE.[MR Control Effectiveness Rating]) Like IIf([Forms]![frm_Main_Menu]![MREALL]="-1-1-1-1","*"))) OR (((tbl_CRAS_MRE.[MR Control Effectiveness Rating])=IIf([Forms]![frm_Main_Menu]![MREALL]="-1-1-1-1",(tbl_CRAS_MRE.[MR Control Effectiveness Rating]) Is Null)));

 

[combo]

Maybe I miss something but your Iif formulas have only two arguments.

combo

 

[xwb]

Re radio buttons and checkboxes - mixing up these two controls is becoming more common. I've seen several GUIs where checkboxes are used with a radio button behaviour and radio buttons are used with a checkbox behaviour, purely because the author didn't like the look of "the other button" or because the author didn't know the difference.

 

[MajP]

. I've seen several GUIs where checkboxes are used with a radio button behaviour and radio buttons are used with a checkbox behaviour, purely because the author didn't like the look of "the other button" or because the author didn't know the difference

Actually, I do not think you understand the difference because there really is none. Radio button, checkboxes, and toggles, all do the same thing. They do 2 states. The difference is how these controls behave when you put them in an option group. An option group can hold any of these types. The difference is once in an option group only one choice can be selected at a time, the others get deselected. Most commonly radio buttons are used in a group and checkboxes are not, but that does not mean you cannot have a bunch of single radio buttons and an option group with checkboxes.

 

[btrini10]

Thanks guys for the responses.
MajP is correct, I used the radio buttons simply because I preferred the look and the intention was to have them as individual options and not related to each other as in an option group.
Still having issues using the "IN" syntax though.
Can anyone assist in explaining why the first syntax works when I use it to build the query criteria but the second gives me the error message that the expression is typed incorrectly or too complex. The difference is the addition of an "IIF" and the syntax seems correct.

In ("Strong","Adequate")

SQL:
SELECT DISTINCT tbl_CRAS_MRE.[Business Area], tbl_CRAS_MRE.[MR Name], tbl_CRAS_MRE.[Source Short Name], tbl_CRAS_MRE.[MR Control Effectiveness Rating]
FROM tbl_CRAS_MRE
WHERE (((tbl_CRAS_MRE.[MR Control Effectiveness Rating]) In ("Strong","Adequate")));


IIf([Forms]![frm_Main_Menu]![MREALL]="-1-100",In ("Strong","Adequate"))

SQL:
SELECT DISTINCT tbl_CRAS_MRE.[Business Area], tbl_CRAS_MRE.[MR Name], tbl_CRAS_MRE.[Source Short Name], tbl_CRAS_MRE.[MR Control Effectiveness Rating]
FROM tbl_CRAS_MRE
WHERE (((tbl_CRAS_MRE.[MR Control Effectiveness Rating])=IIf([Forms]![frm_Main_Menu]![MREALL]="-1-100",(tbl_CRAS_MRE.[MR Control Effectiveness Rating]) In ("Strong","Adequate"))));

Thanks

 

[MajP]

Can anyone assist in explaining why the first syntax works when I use it to build the query criteria but the second gives me the error message that the expression is typed incorrectly or too complex. The difference is the addition of an "IIF" and the syntax seems correct.

You can calculate a field but you cannot calculate syntax. You could not dynamically try to get a table name.
Select field1 from SomeFunctionReturnsTableName([field2])
But besides that, it does not even make any sense. Assuming MREAL = "-1-100" what do you think the sql string would resolve to?

WHERE tbl_CRAS_MRE.[MR Control Effectiveness Rating] = tbl_CRAS_MRE.[MR Control Effectiveness Rating] In ("Strong","Adequate")

If you want to filter a form based on multiple radio buttons, I would do the following. You can have hundreds or thousands of combinations with the same code.
In each radio button tag property put the value you want. In my example I have categories of products in the Northwind database. My example has three options buttons but can have as many as you want. Just name them opt1, opt2,.... optN

Public Function FilterForm()
  'there are options buttons number opt1 to opt3
  Dim i As Integer
  Dim strWhere
  
  For i = 1 To 3
    Debug.Print Me.Controls("opt" & i).Value
    If Me.Controls("opt" & i) Then
       If strWhere = "" Then
         strWhere = "'" & Me.Controls("opt" & i).Tag & "'"
       Else
         strWhere = strWhere & ", '" & Me.Controls("opt" & i).Tag & "'"
       End If
    End If
  Next i
  
  If Not strWhere = "" Then
    strWhere = "Category IN (" & strWhere & ")"
    Debug.Print strWhere
    Me.Filter = strWhere
    Me.FilterOn = True
  Else
    Me.Filter = ""
    Me.FilterOn = False
  End If
End Function

Hilite each radiobutton and in the afterupdate event put =FilterForm

Bottom line as I select radio buttons I make a string like this and make it my form filter. If I unselect all of them then it does not apply the filter.
Category IN ('Beverages')
Category IN ('Canned Meat', 'Beverages')
Category IN ('Condiments', 'Canned Meat', 'Beverages')

 

[MajP]

see demo