Nested Select Case Alternative

[litrelord]

Hello people,

I'm writing a custom function that checks three criteria and assigns a boolean value based on the outcome. At the moment I have nested Select Case statements to check these but I started wondering whether there was a better alternative.

as an example:
criteria 1 as a supplier name
criteria 2 as a product
criteria 3 as a date

if a particular product comes from supplier 1 or 2 then i'd set to true otherwise false.

I thought about putting together a table but there are instances where the flag is set to true only when it's between a specific date range so I wasn't sure how this could be incorporated.

Any ideas for alternatives I may not have thought of?

Thanks

Nick

 

[Joe4]

If there is a specific order/precedence with your three conditions, I think you will be better off using VBA than trying to create some sort of lookup table.

Creating a lookup table works well if you only have one condition, but a lot of different posibilities under that one condition. If each of your conditions only has a few different possibilities, I would stick with the VBA.

That's just my personal preference anyway, FWIW...

 

[Norie]

Nick

If you haven any current code for the function could you post it?

 

[litrelord]

It may well be the most efficient in the end. Just seems really messy having so many select statements to go through.

Thanks

Nick

 

[litrelord]

Hi Norie,

here's what I have so far. I've had to change the variable names etc so if I've missed somethign and it no longer makes sense I apologise. I haven't included the date parts yet but will be soon.

Function MailoutInclude(sProductCode As String, sClientCompany As String, sRange As String, dAssistDate As Date) as Boolean



Select Case sClientCompany


Case "SUPPLIER1"

    Select Case sProductCode
        Case "L82", "R48", "R49", "N26", "T13", "T15", "T16", "T20", "R16", "R17", "R33", "R52", "M31", "N27", "R50", "L55", "L73"
        MailoutInclude = True
        Exit Function
    End Select



Case "SUPPLIER2"

    Select Case sProductCode
        Case "L82", "R48", "R49", "N25", "M29", "M30", "M33", "M34", "M36", "F23", "R37", "R48", "R49", "M14", "M47"
        MailoutInclude = True
        Exit Function
    End Select
        




Case "SUPPLIER3", "SUPPLIER4"

    Select Case sProductCode
        Case "R37", "R24", "N40"
        MailoutInclude = True
        Exit Function
        
        
    Case Else
        MailoutInclude = False
    
        
        Select Case sRange

            Case "LANLN"
                If sProductCode = "N15" Or sProductCode = "T11" Then
                    MailoutInclude = True
                    Exit Function
                End If
                

            Case "LANLP"
                If sProductCode = "T31" Or sProductCode = "R27" Then
                    MailoutInclude = True
                    Exit Function
                End If
            

            Case "J16"
                If sProductCode = "T20" Then
                    MailoutInclude = True
                    Exit Function
                End If
                

            Case "J06", "J17", "J16"
                If sProductCode = "T11" Then
                    MailoutInclude = True
                    Exit Function
                End If
                

            Case "LANRS"
                If sProductCode = "R13" Then
                    MailoutInclude = True
                    Exit Function
                End If
            

            End Select
            

        End Select
            

End Select



End Function

Thanks

Nick

P.S. is it not possible to make the code window wider and lose the bottom scroll bar?!?