Access - error in select case function (blank fields)

[SalGal]

I'm working on an Access query; the data is in a linked MySQL database.

Two of the fields are codes (A, M, 1, etc.). There is no translation table, so I did a quick function to supply the "real" meaning of the codes. All of the cases work except where there is no data in the field; the query returns Error# in that field.

Here's the complete function:
Public Function XFERCD(code As String) As String
Select Case code
Case Is = "P"
XFERCD = "Parent is District Employee"
Case Is = "A"
XFERCD = "Moved-Complete Current Yr Only"
Case Is = "V"
XFERCD = "Home Campus Overcrowded"
Case Is = "M"
XFERCD = "Moved-Attended Previous 2 Years"
Case Is = "R"
XFERCD = "Home Campus Rated Unacceptable"
Case Is = "2"
XFERCD = "Bilingual"
Case Is = "1"
XFERCD = "Forbes/TEEMS PK Programs"
Case Is = "3"
XFERCD = "Special Ed"
Case Is = "4"
XFERCD = "Career Technology Program"
Case Is = "5"
XFERCD = "AG Sci/Vet Tech PHS"
Case Is = "7"
XFERCD = "AG Sci/Hort HHS"
Case Is = "8"
XFERCD = "Auto Tech PHS"
Case Is = "9"
XFERCD = "Orchestra CHS"
Case Else (this is what is not working)
XFERCD = "None Given"
End Select
End Function

I've tried Case Else, Case = " ", Case = "", Case Is Null. . . no difference. All return the error.

The table says this field is a text field, 8 characters in length, and will allow zero length.

If anyone has any ideas, I would appreciate it!

 

[randy700]

 Case Else: 
    XFERCD = "None Given"

Randy

 

[dhookom]

I would try something like:

Public Function XFERCD(code As String) As String
    Select Case code & ""
        Case Is = "P"
             XFERCD = "Parent is District Employee"
        Case Is = "A"
             XFERCD = "Moved-Complete Current Yr Only"
        Case Is = "V"
             XFERCD = "Home Campus Overcrowded"
        Case Is = "M"
             XFERCD = "Moved-Attended Previous 2 Years"
        Case Is = "R"
             XFERCD = "Home Campus Rated Unacceptable"
        Case Is = "2"
             XFERCD = "Bilingual"
        Case Is = "1"
             XFERCD = "Forbes/TEEMS PK Programs"
        Case Is = "3"
             XFERCD = "Special Ed"
        Case Is = "4"
             XFERCD = "Career Technology Program"
        Case Is = "5"
             XFERCD = "AG Sci/Vet Tech PHS"
        Case Is = "7"
             XFERCD = "AG Sci/Hort HHS"
        Case Is = "8"
             XFERCD = "Auto Tech PHS"
        Case Is = "9"
             XFERCD = "Orchestra CHS"
        Case Else (this is what is not working)
             XFERCD = "None Given"
End Select
End Function

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

that because it is a Null
Public Function XFERCD(code As String) As String
to
Public Function XFERCD(code As variant) As String

But why not build the translation table on the access side if you can not build it on the sql side?

 

[SalGal]

Yea! The code As variant worked.

Don't know why I didn't do it in a table; just started this way and wanted to make it work!

Thanks to everyone!

 

[randy700]

How are you calling the function?
The code I provided works fine, regardless of what string value I pass -- your were just missing the colon.

Debug.Print XFERCD(" ")
Debug.Print XFERCD("")

Displays "None given."

Randy

 

[MajP]

Randy,
The OP is calling the function from a query so the function fails because the OP is passing a null not a string value. IMHO functions called from queries should always use variant data types as parameters and then you can catch the null records and handle them.