I'm not sure this is the right place to ask. I'm trying to write a report
at
will show the "alpha" [i.e. A, B+, B etc.] based on the numeric value of a
field. I've used IIf statement {=IIf([QG]>93.49,"A"," ")}. Is there any
way
to "nest" the IIf statements? I've tried various syntax approaches, but
'no
joy'. Any ideas?
YOu can nest IIF statements, but you'll quickly get the expression
being too complex for Access to handle. I'd make two suggestions:
1. Use the Switch() function instead of IIF. Switch takes arguments in
pairs (I don't know the limit on how many pairs but it's large); goes
through them left to right; evaluates the first member of the pair as
being TRUE or FALSE, and if it's true, returns the second. So you
could use
Alpha: Switch([QG]>93.49, "A+", [QG] > 89.9, "A", [QG] > 87.49, "A-",
..., [QG] < 25, "Drop the class while you can")
2. Probably better, as it avoids storing the breakpoints in more or
less inaccessible code: create a Grades table with fields Score and
Grade, with Score being the lowest numeric value which merits the
corresponding Grade. Use a "Non Equi Join" query to look up the
corresponding grade:
SELECT <whatever>, Grades.Grade
FROM yourtable, Grades
WHERE Grades.Score = (SELECT Max([G].[Score] FROM Grades AS G WHERE
G.Score <= [yourtable].[QG])
John W. Vinson[MVP]
