value strings as numerical values in Access

[souperstar]

Okay, let me give you some background before I talk about what I need. I have an input file (.xls) that I need to create a report from. I've decided to not do this in jscript or python (more by my professors exclusion of these), and instead do this in Access. The input file contains student information such as: fname, lname, middle, major, course1 name, course1 attempted credit, course1 earned credit, grade1. The fields that are numbered repeat themselves for a total of 10 occurrences for a possible ten courses a student can try in a given semester.

What I need to do in this report is calculate GPA for each record, there are groupings and fields I haven't listed here but I'm trying to keep it simple. I have no problem doing the math, the problem is that "grade1" contains A, B, C, D, F, I or W and all of the +'s and -'s that are associated with the grading scale. "W" represents withdrawal of a course and it doesn't need to be calculated. "I" represents incomplete and doesn't need to be calculated.

How can I turn an "A" into a "4" (or weight it's value as a "4") to use it in a calculation for GPA? Also, how would I put this on a form? I've been all over the expression builder but I can't seem to make it work. Any insight? If you need more info or a sample line of data let me know.

TYIA

 

[EagleKeeper]

Try to insert the following to convert the Grade to a value

4 - (<grade> - 'A')

F or I should be filtered out as a negative value.