How to use IIF in MSAccess / VBA with 5 conditions?

[Wreckx]

Hi guys, need help with the iif in query, i need to display these adjectival rating using the equivalents from a different query, i would like to use iif:

100-90 = Outstanding
89-80 = Very Satisfactory
79-50 = satisfactory
49-30 = unsatisfactory
29 below = poor

I've actually tried this:

Adjectival_Rating:IIF([eval]<=100 And [eval]>=90,“Outstanding”, (IIF([eval]<=89 And [eval]>=80, “Very Satisfactory” , (IIF([eval]<=79 And [eval]>=50, “Satisfactory”, (IIF([eval]<=49 And [eval]>=30, “Unsatisfactory”, (IIF([eval]<=29, “Poor”,”Out of Range”)))))))))

But I can't seem to find what's wrong :| please help

[Wreckx]

haha, finally got it :)
Just have to use the value "eval" and drag it in the FIELD to identify where the value will be coming from.

AdjectivalRating: IIf([eval]<=100 And [eval]>=90,"Outstanding",(IIf([eval]<=89 And [eval]>=80,"Very Satisfactory",(IIf([eval]<=79 And [eval]>=50,"Satisfactory",(IIf([eval]<=49 And [eval]>=30,"Unsatisfactory",(IIf([eval]<=29,"Poor","Out of Range")))))))))

[NeoPa]

Using IIf() can be less involved than that, but frankly an even better solution, as it's available, would be to use Switch() :

AdjectivalRating: Switch([Eval] Between 90 And 100,'Outstanding',
                         [Eval]>=80,'Very Satisfactory',
                         [Eval]>=50,'Satisfactory',
                         [Eval]>=30,'Unsatisfactory',
                         [Eval] Between 0 And 29,'Poor',
                         True,'Out of Range')

NB. This is laid out to view. You would enter it into your query as a single item.

[Mihail]

Or, based on your data:

IIf([eval] <= 29, "Poor", _
    IIf([eval] <= 49, "unsatisfactory", _
        IIf([eval] <= 79, "satisfactory", _
            IIf([eval] <= 89, "Very Satisfactory", _
                "Outstanding"))))

See NeoPa's NB too.

NeoPa
I think that you must use the Between() function also in lines 2-4.

Wreckx
Note that NeoPa's solution is very general for problems like yours.
My solution is specific based on your data ranges (no gaps between ranges)

[NeoPa]

NeoPa:
Using IIf() can be less involved than that, but frankly an even better solution, as it's available, would be to use Switch()

I did say that it's possible to create a better version using IIf(), but I wouldn't recommend it as Switch() is better in all respects. Your suggestion is not a match for the question as it assumes (as the original question does not) that values outside of the range 0 <= X <= 100 are not possible. They need to be catered for in the scenario described by the question.

Mihail:
NeoPa
I think that you must use the Between() function also in lines 2-4.

No. That wouldn't be a solution I could recommend, but it does draw my attention to a very real flaw in my logic. With that in mind here's a new version which (hopefully) reflects the logic correctly :

AdjectivalRating: Switch([Eval] Not Between 0 And 100,'Out of Range',
                         [Eval]<30,'Poor',
                         [Eval]<50,'Unsatisfactory',
                         [Eval]<80,'Satisfactory',
                         [Eval]<90,'Very Satisfactory',
                         True,'Outstanding')