I have a report based on a query that calculates percentages for academic areas (Math, Science, English, etc.) for each term (1 to 4). The report then consists of 4 rows (1 for each term) and in each row is the corresponding percents for the academic areas.

What I want is a text box next to each percent that calculates the letter grade that corresponds to the percent.

Using VB and the OnPage event, I can get the 1st term to display the correct letter grade. However, in the other 3 rows (for terms 2, 3 and 4), the same letter grade is displayed as in Term 1.

Here is a small sample of the code I used to populate the letter grade text box for Math. Obviously it is only looking at the first record and then carrying that letter grade over to the next 3 rows.

If Me.qry_Math_Grade_Percent_Percent > 90 Then Me.MathGrade = "A"
If Me.qry_Math_Grade_Percent_Percent < 90 Then Me.MathGrade = "B"

What can I change to get the correct letter grade in each row?

Any help would be appreciated.

Thanks.

Can you do this using IIf()'s in the underlying query?

Ken

Probably. If that is the way to go then I can do it.

Since I doubt performance will be an issue, I'd do it in the query because it'll put your logic in one spot.

Ken

Done!

IIf([Percent]>=97,"A+",IIf([Percent]<97 And [Percent]>=93,"A",IIf([Percent]<93 And [Percent]>=90,"A-",IIf([Percent]<90 And [Percent]>=87,"B+",IIf([Percent]<87 And [Percent]>=83,"B",IIf([Percent]<83 And [Percent]>=80,"B-",IIf([Percent]<80 And [Percent]>=77,"C+",IIf([Percent]<77 And [Percent]>=73,"C",IIf([Percent]<73 And [Percent]>=70,"C-",IIf([Percent]<70 And [Percent]>=67,"D+",IIf([Percent]<67 And [Percent]>=63,"D",IIf([Percent]<63 And [Percent]>=60,"D-","E")))))))))))) AS Grade

Thanks for pointing me in the right direction.

You're welcome. I know this is a bit late, but could you have used 'between's in place of <>'s?

:)
Ken

echorley,

Try using formula below

=Switch([Field1] Between 1 And 0.9,"A",[Field1] Between 0.89 And 0.8,"B",[Field1] Between 0.79 And 0.7,"C",[Field1] Between 0.69 and 0.00,"F")

Ken,

Don't you think switch function would be more appropriate to use for grading system.

hth,
Michael

Hum... Never tried that Michael. Looks good. I'll have to give it a spin.


Ken

My SQL is very, very limited (that is why I started in VB) so I have never heard of SWITCH. It looks simplier so I will give it a go. Thanks for all your help.

Wait... Switch is a VB function. Is is also used in SQL?

If I was to put this code into a report I don't think it would solve the original problem of populating the textboxes correctly.