How do I convert number grades to Letter grades in a Query (MDB)?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Actually, this is the statement I tried in my query it doesn't work = IIF(
[Meter Reading (md/gL)] >120,"High",IIF( [Meter Reading (md/gL)]
<70,"Low",""),"Good").
What do I do to make set up this in my Query?
 
The field row in your column in the query should look like this
Grade: IIf([Meter Reading (md/gL)] >120,"High",IIF([Meter Reading
(md/gL)]<70,"Low","Good"))
 
Try:


IIF( [Meter Reading (md/gL)] >120,
"High",
IIF( [Meter Reading (md/gL)] <70, "Low", "Good")
)
 
Actually, this is the statement I tried in my query it doesn't work = IIF(
[Meter Reading (md/gL)] >120,"High",IIF( [Meter Reading (md/gL)]
<70,"Low",""),"Good").
What do I do to make set up this in my Query?
Click to expand...

You've gotten two good answers on the IIF() syntax - but let me make a
couple of additional suggestions.

1. The Switch() function is useful for multibranch IF logic. IIF() can
be nested, but once you have four or five possible results, it gets
very complex and very slow! The Switch() function takes arguments in
pairs; evaluates the first member of each pair (working from left to
right), and if it's True returns the second member of the pair and
quits. E.g.

Switch([Meter Reading (md/gL)] > 120, "High", [Meter Reading (md/gL)]
= 70, "Good", True, [Meter Reading (md/gL)] > 0, "Low", True,
Click to expand...
"Invalid or undefined")

2. A table driven solution might be even better; you can create a
table with fields Low, High, Grade and join it using a "non equi
join":

SELECT yourtable.<whatever>, Grades.Grade
FROM yourtable
INNER JOIN Grades
ON yourtable.[Meter Reading (md/gL)] >= Grades.Low AND
yourtable.[Meter Reading (md/gL)] <= Grades.High;

John W. Vinson[MVP]
 
Back
Top