Select Case Trouble

[Guest]

I can't figure this out. Here's my code:


Public Function E1Sgmt(E1ProdCode As String)

Select Case E1ProdCode
Case "001", "004-02"
E1Sgmt = "CN"
Case "002", "004-01", "004-05", "008", "009", "998"
E1Sgmt = "CPP"
Case "003", "004-06"
E1Sgmt = "EQUIP"
Case "004-03", "004-04", "006", "007", "990"
E1Sgmt = "RS"
Case "005"
E1Sgmt = "RN"
Case "010", "991"
E1Sgmt = "ADMIN"
Case "011", "996"
E1Sgmt = "OTHER"
Case Else
E1Sgmt = "Unclassified"
End Select

End Function

Everything works fine except for the "004-01" through "004-06" portions.
They are returning "Unclassified" instead of their respective values. The
real baffler is, when I use the Immediate window and supply the text value of
"004-01", it returns the correct answer, in this case "CPP", however when I
use the function in an update query, and supply the argument, it returns
"Unclassified". E1 Prod Cd is a text field in tblFinData. Here's my update
SQL as well - any ideas?

UPDATE tblFinData SET [E1 Segment] = E1Sgmt([E1 Prod Cd]);

 

[George Nicholson]

First, I'd rule out that any trailing/leading spaces in E1ProdCode might be
causing a problem
Select Case Trim(E1ProdCode)

As you've proven to yourself via the Immediate window, it should work *as
long as it gets the proper values*. So, if that doesn't fix the problem, the
question remains: what "wrong" values is it getting, and why? Maybe set a
breakpoint on (E1Sgmt = "Unclassified") and check E1ProdCode values?

BTW,
Public Function E1Sgmt(E1ProdCode As String) AS STRING
but explicitly returning a String rather than the default Variant variable
won't explain your current issue.

HTH,

 

[Guest]

I see nothing wrong with the code. Since it works correctly in the immediate
window, it works. I would suspect the data. Try putting a breakpoint on the
line in your case else (E1Sgmt = "Unclassified"). When it hits, check to see
what the real value of [E1 Prod Cd]. It may appear to be correct, but I
would also look at the Len([E1 Prod Cd]) to see if there are any space in it.
Or, you could assign the trim(E1ProdCode) to a variable and do your select

 

[Guest]

You're right. There was a space after the "004-0x" values.

Thanks.

I see nothing wrong with the code. Since it works correctly in the immediate
window, it works. I would suspect the data. Try putting a breakpoint on the
line in your case else (E1Sgmt = "Unclassified"). When it hits, check to see
what the real value of [E1 Prod Cd]. It may appear to be correct, but I
would also look at the Len([E1 Prod Cd]) to see if there are any space in it.
Or, you could assign the trim(E1ProdCode) to a variable and do your select

I can't figure this out. Here's my code:


Public Function E1Sgmt(E1ProdCode As String)

Select Case E1ProdCode
Case "001", "004-02"
E1Sgmt = "CN"
Case "002", "004-01", "004-05", "008", "009", "998"
E1Sgmt = "CPP"
Case "003", "004-06"
E1Sgmt = "EQUIP"
Case "004-03", "004-04", "006", "007", "990"
E1Sgmt = "RS"
Case "005"
E1Sgmt = "RN"
Case "010", "991"
E1Sgmt = "ADMIN"
Case "011", "996"
E1Sgmt = "OTHER"
Case Else
E1Sgmt = "Unclassified"
End Select

End Function

Everything works fine except for the "004-01" through "004-06" portions.
They are returning "Unclassified" instead of their respective values. The
real baffler is, when I use the Immediate window and supply the text value of
"004-01", it returns the correct answer, in this case "CPP", however when I
use the function in an update query, and supply the argument, it returns
"Unclassified". E1 Prod Cd is a text field in tblFinData. Here's my update
SQL as well - any ideas?

UPDATE tblFinData SET [E1 Segment] = E1Sgmt([E1 Prod Cd]);