Public Function Compile Error

[Penfold]

I appreciated the help I received through this newsgroup some time ago to
build a module to change a students score into a curriculum level eg score
of 24 gives a grade of "6a". However, I am now struggling to build a module
that does the opposite! That is to take a grade and allocate it a score. I
looked at the first module (listed below) and changed it around to give what
I thought was a logical solution. But it don't work! Error says something
about a missing object. I don't know enough to fix it, could anyone please
point me in the right direction? It would also be useful to know if I can
use the same module for a range of conversions in the same query or does
each conversion need it's own query?

Original Module was...

Public Function GetGrade(AvgScore As Double) As String

AvgScore = Nz(AvgScore, 0)

Select Case AvgScore

Case Is >= 36

GetGrade = "8a"

Case Is >= 34

GetGrade = "8b"

Case Is >= 32

GetGrade = "8c"

Case Is >= 30

GetGrade = "7a"

Case Is >= 28

GetGrade = "7b"

Case Is >= 26

GetGrade = "7c"

Case Is >= 24

GetGrade = "6a"

Case Is >= 22

GetGrade = "6b"

Case Is >= 20

GetGrade = "6c"

Case Is >= 18

GetGrade = "5a"

Case Is >= 16

GetGrade = "5b"

Case Is >= 14

GetGrade = "5c"

Case Is >= 12

GetGrade = "4a"

Case Is >= 10

GetGrade = "4b"

Case Is >= 8

GetGrade = "4c"

Case Is >= 6

GetGrade = "3a"

Case Is >= 4

GetGrade = "3b"

Case Is >= 2

GetGrade = "3c"

Case Is 0

GetGrade = "W"

Case Else

GetGrade = "U"

End Select

End Function

My attempt at reversing it was...

Public Function GetScore(NCLGym As Text) As String
NCLGym = Nz(NCLGym, 0)
Select Case NCLGym
Case Is = "8a"
GetScore = 36
Case Is = "8b"
GetScore = 34
Case Is = "8c"
GetScore = 32
Case Is = "7a"
GetScore = 30
Case Is = "7b"
GetScore = 28
Case Is = "7c"
GetScore = 26
Case Is = "6a"
GetScore = 24
Case Is = "6b"
GetScore = 22
Case Is = "6c"
GetScore = 20
Case Is = "5a"
GetScore = 18
Case Is = "5b"
GetScore = 16
Case Is = "5c"
GetScore = 14
Case Is = "4a"
GetScore = 12
Case Is = "4b"
GetScore = 10
Case Is = "4c"
GetScore = 8
Case Is = "3a"
GetScore = 6
Case Is = "3b"
GetScore = 4
Case Is = "3c"
GetScore = 2
Case Is = "W"
GetScore = 0
Case Else
GetScore = "U"
End Select
End Function

Thanks in anticipation from a Virtual (Basic) Virgin!.

[pietlinden]

My attempt at reversing it was...

>
Public Function GetScore(NCLGym As Text) As String
NCLGym = Nz(NCLGym, 0)
Select Case NCLGym
Case Is = "8a"
GetScore = 36
Case Is = "8b"
GetScore = 34
Case Is = "8c"
GetScore = 32
Case Is = "7a"
GetScore = 30
Case Is = "7b"
GetScore = 28
Case Is = "7c"
GetScore = 26
Case Is = "6a"
GetScore = 24
Case Is = "6b"
GetScore = 22
Case Is = "6c"
GetScore = 20
Case Is = "5a"
GetScore = 18
Case Is = "5b"
GetScore = 16
Case Is = "5c"
GetScore = 14
Case Is = "4a"
GetScore = 12
Case Is = "4b"
GetScore = 10
Case Is = "4c"
GetScore = 8
Case Is = "3a"
GetScore = 6
Case Is = "3b"
GetScore = 4
Case Is = "3c"
GetScore = 2
Case Is = "W"
GetScore = 0
Case Else
GetScore = "U"
End Select
End Function

I wouldn't do this with VB at all. I'd use SQL. As long as you have a
fixed number of possible values, just put all the (textscore, numeric
score) combinations in a table, and then just use a simple SQL
statement.

Something along the lines of:

Option Compare Database

Public Function ConvertScore(ByVal strScore As String) As Integer
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT NumericScore FROM tblScores WHERE TextScore = '" &
strScore & "';"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)

If rs.RecordCount = 0 Then
ConvertScore = 0
Else
ConvertScore = rs.Fields(0)
End If

rs.Close
Set rs = Nothing

End Function

[Penfold]

Thank you for your response.
It worked OK once I changed (NCLGym As Text) to (NCLGym As String).

Regards
Penfold
<pi********@hotmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

My attempt at reversing it was...

Public Function GetScore(NCLGym As Text) As String
NCLGym = Nz(NCLGym, 0)
Select Case NCLGym
Case Is = "8a"
GetScore = 36
Case Is = "8b"
GetScore = 34
Case Is = "8c"
GetScore = 32
Case Is = "7a"
GetScore = 30
Case Is = "7b"
GetScore = 28
Case Is = "7c"
GetScore = 26
Case Is = "6a"
GetScore = 24
Case Is = "6b"
GetScore = 22
Case Is = "6c"
GetScore = 20
Case Is = "5a"
GetScore = 18
Case Is = "5b"
GetScore = 16
Case Is = "5c"
GetScore = 14
Case Is = "4a"
GetScore = 12
Case Is = "4b"
GetScore = 10
Case Is = "4c"
GetScore = 8
Case Is = "3a"
GetScore = 6
Case Is = "3b"
GetScore = 4
Case Is = "3c"
GetScore = 2
Case Is = "W"
GetScore = 0
Case Else
GetScore = "U"
End Select
End Function

I wouldn't do this with VB at all. I'd use SQL. As long as you have a
fixed number of possible values, just put all the (textscore, numeric
score) combinations in a table, and then just use a simple SQL
statement.

Something along the lines of:

Option Compare Database

Public Function ConvertScore(ByVal strScore As String) As Integer
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT NumericScore FROM tblScores WHERE TextScore = '" &
strScore & "';"

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)

If rs.RecordCount = 0 Then
ConvertScore = 0
Else
ConvertScore = rs.Fields(0)
End If

rs.Close
Set rs = Nothing

End Function