I am saying I would not use the SWITCH function at all (at least not for the first part).
I would create a User Defined Function to convert the Total Mark to a letter grade. That UDF would look something like this:
Code:
Function ConvertGradeToLetter(totalMark As Variant) As String
' This function converts a numeric grade from 0-100 to a letter grade
' Check to see if totalMark is a number
If Not IsNumeric(totalMark) Then
ConvertGradeToLetter = "?"
Exit Function
End If
' Check to make sure totalMark is between 0 and 100
If (Nz(totalMark, -1) < 0) Or (totalMark > 100) Then
ConvertGradeToLetter = "?"
Exit Function
End If
' Convert totalMark to letter
Select Case totalMark
Case Is >= 90
ConvertGradeToLetter = "A"
Case Is >= 80
ConvertGradeToLetter = "B"
Case Is >= 70
ConvertGradeToLetter = "C"
Case Is >= 60
ConvertGradeToLetter = "D"
Case Is >= 50
ConvertGradeToLetter = "E"
Case Is >= 40
ConvertGradeToLetter = "F"
Case Else
ConvertGradeToLetter = "G"
End Select
End Function
Then, you would just use this like any other function, i.e.
=ConvertGradeToLetter(90)
or
=ConvertGradeToLetter([TotalMarkField])
Then, for the Comment portion, you have a number of options:
- Create another UDF for this, following the same structure as I show for the one above
- Create a reference/lookup table that lists two columns, the Letter Grade and the Comment, and link this to your query on the value returned from the UDF (might need a second query to do that)
- Use the SWITCH function on the result of the UDF above to return your Comment