INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query using "IN"

Query using "IN"

(OP)
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 IIf([Forms]![frm_Main_Menu]![MREALL]="00-1-1",In ("Weak","None"))

Thanks for the help.

RE: Query using "IN"

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query using "IN"

"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.

RE: Query using "IN"

(OP)
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 (((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)));

Thanks

RE: Query using "IN"

Without going to your WHERE part, wouldn't it be better / easier to have:

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
...


(you have 2 Rating columns sad )
to be

SELECT DISTINCT
[Business Area],
[MR Name],
[Source Short Name],
NZ([MR Control Effectiveness Rating], "None") AS Rating

FROM tbl_CRAS_MRE
...


(1 Rating column smile )
???

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Query using "IN"

(OP)
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)));

RE: Query using "IN"

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

combo

RE: Query using "IN"

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.

RE: Query using "IN"

Quote:

. 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.

RE: Query using "IN"

(OP)
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

RE: Query using "IN"

Quote:

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?

CODE -->

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

CODE

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')

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close