Question Expression too complex - multiple condition IIF - any other ways to do it?

[phinix]

Guys, I need your help again
I have a crosstab query and I want value field to be depanded to combo field on one form. I hope it makes sense.
If user picks a name of the subject from combo field on one form it will opens report that is based on that crosstab query where value field compared to that subject name.

When I built huge expression for this value field, something like:
Subject: (IIF(form_combo=english,"eng_result",IIF(form_combo=biology,"bio_result",.....))

But I have 25 subjects, so at the end it poped up with error that it is too complex...

Is there any other way to build expression long enough to cover all posibilities? Lookup or something? SOmething like "case" function?

 

[Trevor G]

Guys, I need your help again
I have a crosstab query and I want value field to be depanded to combo field on one form. I hope it makes sense.
If user picks a name of the subject from combo field on one form it will opens report that is based on that crosstab query where value field compared to that subject name.

When I built huge expression for this value field, something like:
Subject: (IIF(form_combo=english,"eng_result",IIF(form_combo=biology,"bio_result",.....))

But I have 25 subjects, so at the end it poped up with error that it is too complex...

Is there any other way to build expression long enough to cover all posibilities? Lookup or something? SOmething like "case" function?

What about creating a table with a single column with the values you want it to check for then use the combo to search the list, then set the report to run base on the value of the combo.