Help with Select Case...

[bwc]

I am still trying to learn how to use VBA
I want to get the code from field "sidstrACT_STA_PROG" and return what i have in my select...case function. but i am getting the same return as the sidstrACT_STAT_PROG field. my code is;

Option Compare Database
Option Explicit 
Function activeStatus(strActStat As String) As String
    Dim LNumber As String
    Dim strReturn As String
    strReturn = strActStat
    Select Case LNumber
       Case 1, 2, 3, 4, 5, "A", "C", "D", "E", "F", "H", "K", "N", "P", "R", "S", "T"
          strReturn = "AGR"
       Case 6, 9, "B", "G", "I", "J", "M", "U", "W", "X"
          strReturn = "ADSW"
       Case 7
          strReturn = "ADT"
       Case "Y"
          strReturn = "MDAY"
    End Select
    activeStatus = strReturn
End Function

and my query is;

SELECT tblSIDPERS.sidstrCURR_UPC, tblSIDPERS.sidstrACT_STAT_PROG, 
 activeStatus([sidstrACT_STAT_PROG]) AS Expr1
FROM tblSIDPERS INNER JOIN tblOrganization ON tblSIDPERS.sidstrCURR_UPC = tblOrganization.orgstrUPC;

Both field sidstrACT_STAT_PROG and Expr1 return "A" rather then Expr1 returning "AGR". what is it that I am doing wrong?

Thank you ... most of what I have learned came from this site.

 

[Beetle]

You're evaluating LNumber with your Select Case, however, you never actually supply a value for LNumber (at least not in the posted code). You declare it as a Sting variable at the beginning of the sub but supply no value so when you try to evaluate it with Select Case it is Null, meaning none of the conditions are true and strReturn remains equal to strActStat (which you set right before the Select Case).

 

[bwc]

Thanks Sean, I got it to work