Converting letter grades to scores

[ND1994]

New at this and in education. I would like to take letter grades from a grade field in my table and on a form I use for grade input, and create a new field which would appear as a score for that particular grade. So, If [Grade]=A+, 100, [Grade]= A, 90, etc. Probably an easy thing for most. Can abyone illustrate how to do this. Can it be a new query field or just a form field?

 

[CraigDolphin]

Create a table (tblGradesVsScores) with two fields: Grade and Score. Fill in the score for each possible letter grade. Then, put an unbound text box on your form (txtScore).

Then, in the on_current event of your form and the after_update event of your grade control (txtGrade), put code that looks up the appropriate score for the grade (as long as there is a grade to lookup)

Something like...

if Me.txtGrade & "" <> "" then
Me.txtScore = Dlookup("Score","tblGradesVsScores","[Grade]=" & Me.txtGrade)
end if

Alternatively, create the new table as above, link the Grade field in your existing table with the grade field in the new table in your relationships view. Then create a query that lists all the fields in your old table as well as the Score field of the new table. Then, use the query as the recordset for your form instead of the original table. You should then be able to add a field from the list of fields that will display the score.

 

[MarkK]

CD: Cool null checker!

 

[captain1974]

You could try writing a function which is referred to in a query. The function may look something like this:

Public Function GetGrade(grade As Single) As String
    Select Case grade
        Case Is < 0
            GetGrade = "-Error"
        Case Is < 30
            GetGrade = "D"
        Case Is < 60
            GetGrade = "C"
        Case Is < 70
            GetGrade = "B"
        Case Is < 100
            GetGrade = "A"
        Case Else
            GetGrade = "Error"
    End Select
End Function

Then create a query returning the grade by typing Grade: GetGrade([rawresult]) into the appropriate field. Substituting rawresult for your actual field name that contains the raw results. I think you could build into a form, based on the query, but I haven't tried myself...

 

[Moniker]

The swtich function to the rescue again...

txtLetterScore:
=Switch([numGradeValue]=100,"A+"
,[numGradeValue]>=90 AND [numGradeValue]<=99,"A"
,[numGradeValue]>=80 AND [numGradeValue]<=89,"B"
,[numGradeValue]>=70 AND [numGradeValue]<=79,"C"
,[numGradeValue]>=60 AND [numGradeValue]<=69,"D"
,[numGradeValue]<60,"F"
,TRUE, "No numeric grade")

You can place that straight in a query -- no extra functions or anything needed. Look up the Switch Function in help to see exactly what is going on there.