IIf and Switch issues with Yes/No Column Criteria

[padlocked17]

I have a combo box that selects a record on a form.

The combo box is based off of a query and I want to be able to filter a field on the query that the combo box is being populated by.

The problem is that it's a yes/no field I want to setup criteria for and I haven't been able to get an IIf or Switch statement to work correctly.

The issue is that I want to filter the combo box from either two separate combo boxes and a check box or from a group of radio buttons or a combination of the two.

The idea was to have a drop down with a list of enrolled members. The second drop down would requery the first one and would allow you to select a class. The third drop-down would allow you to select, "All", "Students", or "Teachers". The check box would then allow you to view archived members who have a "Yes" in the Graduated column of the query.

The class drop-down I have figured out fine on how to filter. The issue lies within the selection of a member type. I tried using radio buttons for "All", "Students" and "Teachers" but the problem was when I built my IIf or Switch statement, I couldn't figure out how to make the "All" values not put any criteria into the query. My statements looked like below:

Switch([Forms]![frmHome]![fraFilterSelect]=1,"",[Forms]![frmHome]![fraFilterSelect]=2,No,[Forms]![frmHome]![fraFilterSelect]=3,Yes)

IIf([Forms]![frmHome]![fraFilterSelect]=1,"",IIf([Forms]![frmHome]![fraFilterSelect]=2,No,IIf([Forms]![frmHome]![fraFilterSelect]=3,Yes,"")))

Neither worked out. Essentially I am trying to set criteria for a Yes/No column, and I can't get it to work correctly.

I am requerying after update of the radio button option group and/or the combo box filter.

Any ideas?

 

[ajetrumpet]

I tried using radio buttons for "All", "Students" and "Teachers" but the problem was when I built my IIf or Switch statement, I couldn't figure out how to make the "All" values not put any criteria into the query.

Russ:

If you don't want to set any criteria, then just put the field value (associated with the radio button being evaluated) back into the IIF function, like this:

IIF(ALLButtonValue = SomeNumber, MyTable.MyField, "False" Value

You had set the value to "", which does specify a criteria. It says zero length string, and that's different than the original field value.

 

[padlocked17]

Awesome, I'll give that a whirl. I certainly wouldn't have thought of that in a million years.

Thanks!

 

[ajetrumpet]

Russ,

Just remember that if you're going to reference the actual field that was evaluated with the IIF function (with another field, or within another function), refer to the alias of the IIF that is assigned, instead of the actual field name. Good luck with it!

 

[padlocked17]

Adam -

I didn't quite follow that. I'm placing the IIf statement in the Graduated column and will be referencing the tblEnrollment.Graduated column. What/how should I go about that?

Thanks!

 

[ajetrumpet]

Russ,

All I am saying, is that if you want to refer to the VALUES that have been created in the column that was populated with the IIF statement, then when you reference that column, you have to use the ALIAS NAME instead of the actual "source column" that the IIF function pulled the values from. So, if I have a function in a query like this...

SELECT table1.field1, IIF(forms!myform!mybutton1 = 1, 
   table1.[U]field2[/U], "conditionWasn'tMet") [color=red]AS [ButtonValue][/color]

FROM table1;

...when I want to refer to the "IIF function" column, I have to use the name ButtonValue instead of the source field name, which is field1. See what I mean? If you refer to "field2", you will get the values that are actually housed in the table, and not the values (in that same field) that have been manipulated (or nott) by the function IIF.

 

[padlocked17]

Adam -

Thanks a ton. That did exactly what I was looking for.

I appreciate it.