Access 2003 report - Nested Select Statements (1 Viewer)

[krazykasper]

I am trying to define several categories of product (Parts, Bndl, E1, Ink, and Paper). The following are fields in one of my tables: MaterialId, MaterialDesc, and ProductHierarchy.
I have the following VBA case statement that works well in my report. However, when I include two additional Select Case statements (See 2nd set of code following 1st set) it compiles without errors but my report shows "#Error".

=====================================================

Public Function MyTonerCategory()

Dim TonerCategory As String

Select Case MaterialId
Case "4511100003", "4511100015", "4511100020", "4511100027", "4511100037", "4511100045", "45111X0154"
MyTonerCategory = "Bndl"
Case "7015598"
MyTonerCategory = "E1"
Case Else
MyTonerCategory = "Parts"
End Select

End Function

======================================================

Public Function MyTonerCategory()

Dim TonerCategory As String

Select Case MaterialId
Case "4511100003", "4511100015", "4511100020", "4511100027", "4511100037", "4511100045", "45111X0154"
MyTonerCategory = "Bndl"
Case "7015598"
MyTonerCategory = "E1"
Case Else
MyTonerCategory = "Parts"
End Select

Select Case ProductHierarchy
Case When Like "?8S78*"
MyTonerCategory = "Ink"
End Select

Select Case MaterialDesc
Case When Like "*BOND*" Or "BND*"
MyTonerCategory = "Paper"
End Select

End Function

=====================================================

Krazy (Bill) Kasper

 

[DJkarl]

I am trying to define several categories of product (Parts, Bndl, E1, Ink, and Paper). The following are fields in one of my tables: MaterialId, MaterialDesc, and ProductHierarchy.
I have the following VBA case statement that works well in my report. However, when I include two additional Select Case statements (See 2nd set of code following 1st set) it compiles without errors but my report shows "#Error".

=====================================================

Public Function MyTonerCategory()

Dim TonerCategory As String

Select Case MaterialId
Case "4511100003", "4511100015", "4511100020", "4511100027", "4511100037", "4511100045", "45111X0154"
MyTonerCategory = "Bndl"
Case "7015598"
MyTonerCategory = "E1"
Case Else
MyTonerCategory = "Parts"
End Select

End Function

======================================================

Public Function MyTonerCategory()

Dim TonerCategory As String

Select Case MaterialId
Case "4511100003", "4511100015", "4511100020", "4511100027", "4511100037", "4511100045", "45111X0154"
MyTonerCategory = "Bndl"
Case "7015598"
MyTonerCategory = "E1"
Case Else
MyTonerCategory = "Parts"
End Select

Select Case ProductHierarchy
Case When Like "?8S78*"
MyTonerCategory = "Ink"
End Select

Select Case MaterialDesc
Case When Like "*BOND*" Or "BND*"
MyTonerCategory = "Paper"
End Select

End Function

=====================================================

Krazy (Bill) Kasper

Despite the fact your code compiled Like is not valid with a Select Case statement.

Case When Like "?8S78*"

The program thinks When is a variable in this case, and it is comparing the value for ProductHierarchy to the expression (When Like "?8S78*") which will be False. If you add Option Explicit to the top of your code module you will see your code will no longer compile as When is not declared anywhere. You can change your second two Select case statements to If statements and it should work.