Compound Multiple If Statement 1

[sxschech]

Is there a cleaner way to write a compound if statement or should I use another syntax altogether?
In particular, this line of code.
[tt]
ElseIf Mid(GroupID, 3, 2) = "AP" Or Mid(GroupID, 3, 2) = "SJ" Or Mid(GroupID, 3, 2) = "SQ" Then
[/tt]

Function RegionCode(RTCol As String, GroupID) As String
    If Len(GroupID) > 0 Then
        If RTCol = "PS" Then
            If Mid(GroupID, 1, 3) = "X30" Then
                RegionCode = "PS - ISME - HP"
            ElseIf Mid(GroupID, 2, 2) >= 15 And Mid(GroupID, 2, 2) <= 19 Then
                RegionCode = "PS - OAKLAND"
            ElseIf Mid(GroupID, 2, 2) <= 29 Then
                RegionCode = "PS - SAN RAMON"
            ElseIf Mid(GroupID, 2, 2) <= 49 Then
                RegionCode = "PS - SOUTH BAY"
            ElseIf Mid(GroupID, 2, 2) <= 79 Then
                RegionCode = "PS - SACRAMENTO"
            ElseIf Mid(GroupID, 2, 2) <= 89 Then
                RegionCode = "PS - NORTH BAY"
            ElseIf Mid(GroupID, 2, 2) >= 90 Then
                RegionCode = "PS - MULTI-REGION"
            End If
        ElseIf RTCol = "ED" Then
            If GroupID = "LAPSA" Or Mid(GroupID, 3, 2) = "LA" Then
                RegionCode = "ED - LOS ANGELES"
[red]            ElseIf Mid(GroupID, 3, 2) = "AP" Or Mid(GroupID, 3, 2) = "SJ" Or Mid(GroupID, 3, 2) = "SQ" Then
                RegionCode = "ED - SOUTH BAY" [/red]
            ElseIf Mid(GroupID, 3, 2) = "SR" Then
                RegionCode = "ED - SANTA ROSA"
            End If
        End If
    Else
        RegionCode = ""
    End If

 

[jadams0173]

I think a form of SELECT....CASE would work for you.

Not tested just and example.

Function RegionCode(RTCol As String, GroupID) As String
dim NewVar as string
    If Len(GroupID) > 0 Then
        If RTCol = "PS" Then

            NewVar=Mid(GroupID,1,3)
            
            Select Case NewVar
                case "X30"
                   RegionCode="PS - ISME - HP"

I tried to have patience but it took to long! -DW

 

[sxschech]

Using a Case Statement would still leave me with the same question, how to deal syntax wise with "OR" or Between situations, would I need separate lines? If I were using a query I could say IN ("SP","SJ","SQ") is there an equivalent method to do this with VBA in the function?

[tt]
1.
ElseIf Mid(GroupID, 3, 2) = "AP" Or Mid(GroupID, 3, 2) = "SJ" Or Mid(GroupID, 3, 2) = "SQ" Then

2.
If GroupID = "LAPSA" Or Mid(GroupID, 3, 2) = "LA"
[/tt]

 

[SgtJarrow]

Since most of your "cases" are the same (Mid(GroupID, 3, 2)), you could define a variable and fill the variable with the Mid(GroupID, 3, 2) statement.

You can then shorten your OR statements, or in a SELECT as jadams mentioned:

Select Case VariableName
Case "AP", "SJ", "SQ"
RegionCode = Something
End Select

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

Something like:

Function RegionCode(RTCol As String, GroupID) As String
    If Len(GroupID) > 0 Then
        Dim strID As String
        If RTCol = "PS" Then
            If Mid(GroupID, 1, 3) = "X30" Then
                RegionCode = "PS - ISME - HP"
                strID = Mid(GroupID, 2, 2)
                Select Case strID
                    Case 15 To 19: RegionCode = "PS - OAKLAND"
                    Case 20 To 29: RegionCode = "PS - SAN RAMON"
                    ' ETC
                End Select
        ElseIf RTCol = "ED" Then
            strID = Mid(GroupID, 3, 2)
            If GroupID = "LAPSA" Then
                RegionCode = "ED - LOS ANGELES"
            Else
                Select Case strID
                    Case "LA": RegionCode = "ED - LOS ANGELES"
                    Case "AP", "SJ", "SQ": RegionCode = "ED - SOUTH BAY"
                    ' ETC
                End Select
            End If
        End If
    Else
        RegionCode = ""
    End If

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[SgtJarrow]

Sorry. I missed an Else before the first strID = Mid(GroupID, 2, 2) and an End If after the first End Select.


If RTCol = "PS" Then
If Mid(GroupID, 1, 3) = "X30" Then
RegionCode = "PS - ISME - HP"
Else
strID = Mid(GroupID, 2, 2)
Select Case strID
Case 15 To 19: RegionCode = "PS - OAKLAND"
Case 20 To 29: RegionCode = "PS - SAN RAMON"
' ETC
End Select
End If

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[sxschech]

To be sure I understand... to signify OR, the answer is to separate the items by a comma.

 

[SgtJarrow]

In a Select Case, yes....you can range numbers by
Case Is <= 15
Something
Case 16 to 25
Something
Case Is >= 26
Something

But for strings, you separate each "case" by a comma
Case "this", "and", "that"

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[sxschech]

Great, thanks.