Populate textbox

[echorley]

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.

 

[KenHigg]

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

Ken

 

[echorley]

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

 

[KenHigg]

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

 

[echorley]

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.

 

[KenHigg]

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


Ken

 

[Ukraine82]

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

 

[KenHigg]

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


Ken

 

[echorley]

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.