By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,742 Members | 1,033 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,742 IT Pros & Developers. It's quick & easy.

Public Function Error

P: n/a
Hi,
I benefited from this newsgroups help to compile a public function to
convert numeric scores to alphanumeric grades. Now I need to reverse the
process to allocate a numeric score to each alphanumeric grade.
I tried this below but it just spits out #error for each record. I tried
changing "(NCLGym As Double)" to "As Text" but error message said "User
defined type not defined".

This is my limit and any help would be appreciated.

Thanks
Public Function GetScore(NCLGym As Double) 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

Sep 12 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
This seems to work... mind you I'm a lazy bastard, so I didn't test it
very thoroughly.

Public Function GetScore(NCLGym As String) As Variant

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

Sep 12 '06 #2

P: n/a
Penfold wrote:
Hi,
I benefited from this newsgroups help to compile a public function to
convert numeric scores to alphanumeric grades. Now I need to reverse the
process to allocate a numeric score to each alphanumeric grade.
I tried this below but it just spits out #error for each record. I tried
changing "(NCLGym As Double)" to "As Text" but error message said "User
defined type not defined".

This is my limit and any help would be appreciated.

Thanks
Public Function GetScore(NCLGym As Double) 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
You realize you've declared "NCLGym As Double" but each of your
comparisons to NCLGym is against a string?
I tried changing "(NCLGym As Double)" to "As Text"
OK, I see you did think about that. The type you want is String, not Text.

Try changing to "(NCLGym As String)", assuming NCLGym really /is/ a
String type.
--
Smartin
Sep 12 '06 #3

P: n/a
yes, good catch. that's what happens when you program and watch TV at
the same time.

Sep 13 '06 #4

P: n/a
pi********@hotmail.com wrote:
yes, good catch. that's what happens when you program and watch TV at
the same time.
LOL (^: But you got the right answer!

--
Smartin
Sep 13 '06 #5

P: n/a
Thanks very much to Smartin and pietlinden for the help.
It sorted the problem out but also showed that the Nz line doesn't handle
empty fields, these still come back as #Error
Any thoughts appreciated (not sure if this should be a separate post).
Anything else I type in is trapped by Case else OK.

Thanks again.
"Smartin" <sm********@yahoo.comwrote in message
news:25******************************@giganews.com ...
pi********@hotmail.com wrote:
yes, good catch. that's what happens when you program and watch TV at
the same time.

LOL (^: But you got the right answer!

--
Smartin

Sep 13 '06 #6

P: n/a
Penfold wrote:
Thanks very much to Smartin and pietlinden for the help.
It sorted the problem out but also showed that the Nz line doesn't handle
empty fields, these still come back as #Error
Any thoughts appreciated (not sure if this should be a separate post).
Anything else I type in is trapped by Case else OK.

Thanks again.
"Smartin" <sm********@yahoo.comwrote in message
news:25******************************@giganews.com ...
>pi********@hotmail.com wrote:
>>yes, good catch. that's what happens when you program and watch TV at
the same time.
LOL (^: But you got the right answer!

--
Smartin

Ahh.. it's no use doing
NCLGym = Nz(NCLGym, 0)
in the VB code when the NCLGym type (such as String) can't handle nulls.
You could change the input parameter to be Variant or (my preference)
take care of the null up front in the query:
SELECT GetScore(NZ(NCLGym,0))

--
Smartin
Sep 13 '06 #7

P: n/a
Thanks again, your help is appreciated.

Penfold
"Smartin" <sm********@yahoo.comwrote in message
news:3Z******************************@giganews.com ...
Penfold wrote:
Thanks very much to Smartin and pietlinden for the help.
It sorted the problem out but also showed that the Nz line doesn't
handle
empty fields, these still come back as #Error
Any thoughts appreciated (not sure if this should be a separate post).
Anything else I type in is trapped by Case else OK.

Thanks again.
"Smartin" <sm********@yahoo.comwrote in message
news:25******************************@giganews.com ...
pi********@hotmail.com wrote:
yes, good catch. that's what happens when you program and watch TV at
the same time.

LOL (^: But you got the right answer!

--
Smartin

Ahh.. it's no use doing
NCLGym = Nz(NCLGym, 0)
in the VB code when the NCLGym type (such as String) can't handle nulls.
You could change the input parameter to be Variant or (my preference)
take care of the null up front in the query:
SELECT GetScore(NZ(NCLGym,0))

--
Smartin

Sep 14 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.