How to convert integer value to letter value for grades

[obigeek]

I am a teacher trying to do my grades in Access 2000 and have hit a snag - I've figured out how to calculate the grades (with a linked Excel worksheet) using a 0-4 point scale, but want the grades to be displayed as a A-F value.

I did this in Excel with a formula (example):
=IF(COUNT(Y2)=0,"",IF(Y2>3.4,"A",IF(Y2>2.7,"B",IF(Y2>1.7,"C",IF(Y2>0.9,"D","F")))))
which took care of not counting blank (grade) assignments and making sure the final average only calculated graded items.

I am trying to accomplish the same thing in Access, focusing primarily on the IIf function. Maybe a macro? I'm trying to learn! I've tried several IIf variants, but the best one gave a circular reference error.

Basically, all I want is if the value is Null, the field in the report will be blank. If the value is 0, and "F" is displayed, if it is 1, a "D" is displayed, if it is a 2, a "C" is displayed, if it is 3, a "B" is displayed, and if it is 4, an "A" is displayed. The report is from a table with grades recorded as 0,1,2,3,4, or blank.

Any suggestions?

 

[fneily]

You could do this: Under the Detail section of your report, create a textbox (let's say it's calle GradeBox).
Create a macro. Click the Conditions button. The first condition will be Grade = 0. The next cell you'll have SetValue. For parameters, Item will be GradeBox.Value,
Expression will be F. Then do the next condition. Etc.
For blank, you could set it to N/A.
Save the macro, name it.
Then click on the Detail BAR. Bring up the property sheet, click Events and place the Macro on the OnFormat event.
Now before it prints, it'll check to see the grade and make the translation in the macro and print out the appropriate letter grade.
I'm doing this from memory, but it should work.

 

[Lilliabeth]

As an FYI, because understanding Immediate Ifs is a good thing, in the control source of a textbox on the report:

=IIf([intGrade]=0,"F",IIf([intGrade]=1,"D",IIf([intGrade]=2,"C",IIf([intGrade]=3,"B",IIf([intGrade]=4,"A","")))))

 

[fneily]

Or that.

 

[PHV]

Another way:
IIf(IsNumeric([grade], Choose(1+[grade],"F","E","D","C","B","A"), "")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[obigeek]

Wow! I had no idea I would get such fast responses!

Thanks Lilliabeth for your IIf suggestion. Yours is very similiar to the one I was trying (in various forms) and encouraging that I was on the right, if somewhat slow, track. The ending with double quotation marks sure simplified my no value scenario.

It turns out my snag was a circular reference error that I corrected by renaming the control box. I think I understand.

Thank you fneily for your macro suggestion. I looked at that option briefly but thought I was over my head. Your idea looks neat so I'm going to play with it too - it should come in handy.

PHV, um, I think I understand where you started from, but I fizzled out fast. Maybe when I get better at this I'll better understand your insight.

Thanks again! I can't wait for show-and-tell on Monday!

 

[PHV]

In fact my formula didn't satisfy your request as you don't want "E" as return value:
IIf(IsNumeric([grade], Choose(1+[grade],"F","D","C","B","A"), "")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886