Can I use ElseIf vice IIF or get results for 3 or more cases in calc query field? (1 Viewer)

[bpd]

I have a query where the first column is an integer type number 0-100 representing a percentage. I have learned how to create a second field that converts this integer into letter representation (see below):

QualPercent:IIf([PercentComplete]=100,"Q","NQ")

This works fine. However, this is only for two cases. Any idea how to do the same sort of thing for three or more variables? I tried using If and ElseIf statements in query box, but couldn't make it work.

I'd like to set 100 = Q
99 = I
0-98 = UI
null = NQ

I'm thinking I can keep combining IIf statements and replace the "NQ" in the first statement above with a second IIf statement and so on and so on...

Thanks,
Brian

 

[Jon K]

QualPercent: IIf([PercentComplete]=100,"Q", IIf([PercentComplete]=99,"I", IIf([PercentComplete] Between 0 And 98," UI", "NQ")))
.

 

[bpd]

Thanks a lot John.

 

[The_Doc_Man]

In the long run, the "better" way is to avoid this logic entirely and create a table (perhaps a one-time creation) with the percentages from 0 to 100 (i.e. only 101 rows) and the letter you want to associate with each. Then JOIN the two tables on the percentage row, which should be an integer in the stated range. So the JOIN would be legal. Further, the table would be pretty "narrow" so you would have very little performance problem. Narrow tables are easy to use.

Even if you have to do a two-stage query (compute first stage, JOIN second stage), you get the right result with no IIF logic whatsoever.

Not only that, but if this is a case of grading on a curve (or different standards for different tests), you can have one column for each test standard in the lookup table and just select the column appropriate for the test you are using.