By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

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

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Jun 5 '12 #1
Share this Question
Share on Google+
4 Replies


P: 2
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.
Expand|Select|Wrap|Line Numbers
  1. 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")))))))))
Jun 5 '12 #2

NeoPa
Expert Mod 15k+
P: 31,186
Using IIf() can be less involved than that, but frankly an even better solution, as it's available, would be to use Switch() :
Expand|Select|Wrap|Line Numbers
  1. AdjectivalRating: Switch([Eval] Between 90 And 100,'Outstanding',
  2.                          [Eval]>=80,'Very Satisfactory',
  3.                          [Eval]>=50,'Satisfactory',
  4.                          [Eval]>=30,'Unsatisfactory',
  5.                          [Eval] Between 0 And 29,'Poor',
  6.                          True,'Out of Range')
NB. This is laid out to view. You would enter it into your query as a single item.
Jun 5 '12 #3

100+
P: 759
Or, based on your data:
Expand|Select|Wrap|Line Numbers
  1. IIf([eval] <= 29, "Poor", _
  2.     IIf([eval] <= 49, "unsatisfactory", _
  3.         IIf([eval] <= 79, "satisfactory", _
  4.             IIf([eval] <= 89, "Very Satisfactory", _
  5.                 "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)
Jun 6 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. AdjectivalRating: Switch([Eval] Not Between 0 And 100,'Out of Range',
  2.                          [Eval]<30,'Poor',
  3.                          [Eval]<50,'Unsatisfactory',
  4.                          [Eval]<80,'Satisfactory',
  5.                          [Eval]<90,'Very Satisfactory',
  6.                          True,'Outstanding')
Jun 6 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.