expression with multiple if / else statements

[ns5000]

In my database, the value of the "teacher" variable is based on three different variables: grade(9,10,11 or 12); floor (1 or 2); and honor ("no" or "yes").

• There are only 4 grades (9 through 12) and 2 floors (floor 1 and floor 2)
• Ms. Johnson teaches all the honors students, regardless of their floor and regardless of their grade.
• Ms. Williams teaches all the floor 1 students in grades 9 through 11 (but not the honors students, of course).
• Mr. Thomas teaches all the floor 1 students in grade 12 (but not the honors students, of course).
• Ms. Sanchez teaches all the floor 2 students in grades 9 and 10 (but not the honors students, of course).
• Ms. Anderson teaches all the floor 2 students in grades 11 and 12 (but not the honors students, of course).

From time to time, a student's floor and grade could change, and students can be added to the honor program (or removed) any time. Thus, I want Access to automatically determine the value of for the "teacher" variable based on grade, floor, and honor.



I understand how to use the expression builder in a query to have Access do mathematical calculations such as adding tax to a base price, but I don't know how to use the expression builder when it involves text strings (no math to calculate) and multiple IF conditions, with different values for those different conditions.

How can I do this in the expression builder? Is a query with the expression builder the best route I should take?

If honor="yes" then teacher="Johnson"
else if floor=1 and grade in (9,10,11) then teacher="Williams"
else if floor=1 and grade=12 then teacher="Thomas"
else if floor=2 and grade in (9,10) then teacher="Sanchez"
else if floor=2 and grade in (11,12) then teacher="Anderson"
else teacher=""

[June7]

Do you want this expression in a query or VBA? In query, use nested IIf() function or Switch() function. VBA would use If ElseIf Else structure or Switch() function.

honor, grade, floor are fields in table?

I don't use expression builder.

[GinaWhipp]

Hmm, this makes me a bit nervous. Suppose a teacher quits, you have to go in and change the code every time. Thinking you could have a table for the Teachers and another table for Teacher's Appointments which would hold which grades they teach and if they teach honor students or not plus whatever else you want. Then you could just use a query and maybe a DLookup() to find the appropriate Teacher. And, if the Teacher changes or starts teaching another grade or on another floor it's a check box or combo box change away without bothering with the code.

Just my two cents worth...

[June7]

So, Gina makes good point. The "best route" is not to hard code data that can change. Use a table that would be referenced in whatever conditional expression is constructed.

[ArviLaanemets]

SCRAP IT! A wrong forum!


Easiest way will be to add a "Setup" sheet with tables where you can assign a numeric value to every of those variables, and you can use those numeric values to calculate teacher's "value" using some simple mathematical formula (like adding/multiplying) - completely avoiding IF's.

Another way is use mathematical/logical expressions, like:

=(Grade - 8)*Floor*(1+(Honor = "yes"))

, or whatever. Replace Grade/Floor/Honor with cell references. And in case you use non-numeric values for Grade/Floor, use IF's for those parts of formula only <like IF(Floor = "floor 1",1,2)>, or MATCH <like MATCH(Floor,{"floor 1","floor2"}, 0)>, or again mathematical/logical expressions <like (1*(Floor = "floor 1")+2*(Floor = "floor 2")). Formula may got quite long, but mathematical/logical operations are fastest in Excel, so your file won't slow down compared with using IF's, and there will be no limit of conditions (other than the length of formula) you can apply!

[ns5000]

Thanks! The SWITCH function works great. Thanks also to everyone else who chimed-in.