Switch Function too complex

[skwilliams]

I'm trying to use a switch function to score individuals. It calculates a score based on LlSl (a skill level), and falling with this range LlNeMPO (Minutes per order) to get LlPrRa (Score).

This switch function produces the error "The expression you entered is too complex."

Any workarounds to keep the calculations within the query??

LlPrRa: Switch (LlSl=1 and LlNeMPO>=2.8752, 1, LlSl=1 and LlNeMPO>=2.5001 and LlNeMPO <=2.8751, 2,  LlSl=1 and LlNeMPO>=2.125 and LlNeMPO <=2.5, 3,  LlSl=1 and LlNeMPO>=1.75 and LlNeMPO <=2.1249, 4,  LlSl=1 and LlNeMPO <=1.7499, 5, LlSl=2 and LlNeMPO>=2.5302, 1, LlSl=2 and LlNeMPO>=2.2001 and LlNeMPO <=2.5301, 2,  LlSl=2 and LlNeMPO>=1.87 and LlNeMPO <=2.2, 3,  LlSl=2 and LlNeMPO>=1.54 and LlNeMPO <=1.8699, 4,  LlSl=2 and LlNeMPO <=1.5399, 5, LlSl=3 and LlNeMPO>=2.0702, 1, LlSl=3 and LlNeMPO>=1.8001 and LlNeMPO <=2.0701, 2,  LlSl=3 and LlNeMPO>=1.53 and LlNeMPO <=1.8, 3,  LlSl=3 and LlNeMPO>=1.26 and LlNeMPO <=1.5299, 4,  LlSl=3 and LlNeMPO <=1.2599, 5, LlSl=4 and LlNeMPO>=1.7252, 1, LlSl=4 and LlNeMPO>=1.5001 and LlNeMPO <=1.7251, 2,  LlSl=4 and LlNeMPO>=1.275 and LlNeMPO <=1.5, 3,  LlSl=4 and LlNeMPO>=1.05 and LlNeMPO <=1.2749, 4,  LlSl=4 and LlNeMPO <=1.0499, 5)

 

[boblarson]

Personally I would use a function (put this in a standard module and name the module something other than the function name):

Function GetL1PrRA(dblL1S1 As Double, dblL1NeMPO As Double) As Integer
    Dim intA   As Integer
    Select Case dblL1S1
        Case 1
            Select Case dblL1NeMPO 
                Case Is <= 1.7499
                    intA = 5
                Case 1.75 To 2.1249
                    intA = 4
                Case 2.125 To 2.5
                    intA = 3
                Case 2.5001 To 2.8751
                    intA = 2
                Case Is >= 2.8752
                    intA = 1
            End Select
        Case 2
            Select Case dblL1NeMPO 
                Case Is <= 1.5399
                    intA = 5
                Case 1.54 To 1.8699
                    intA = 4
                Case 1.87 To 2.2
                    intA = 3
                Case 2.2001 To 2.5301
                    intA = 2
                Case Is >= 2.5302
                    intA = 1
            End Select
        Case 3
            Select Case dblL1NeMPO 
                Case Is <= 1.2599
                    intA = 5
                Case 1.26 To 1.5299
                    intA = 4
                Case 1.53 To 1.8
                    intA = 3
                Case 1.8001 To 2.0701
                    intA = 2
                Case Is >= 2.0702
                    intA = 1
            End Select
        Case 4
            Select Case dblL1NeMPO 
                Case Is <= 1.0499
                    intA = 5
                Case 1.05 To 1.2749
                    intA = 4
                Case 1.275 To 1.5
                    intA = 3
                Case 1.5001 To 1.7251
                    intA = 2
                Case Is >= 1.7252
                    intA = 1
            End Select
    End Select
    GetL1PrRA = intA
End Function

and then just call it in your query:

L1PrRa:GetL1PrA([L1S1], [L1NeMPO])

and then go from there.

 

[skwilliams]

I set it up as you suggested, now I get "Undefined function 'GetLlPrA' in expression"

 

[skwilliams]

I got it. The function was misspelled.

Thanks.

 

[boblarson]

I got it. The function was misspelled.

Thanks.

Yeah, it isn't easy to distinguish a one (1) from the letter I at times and I thought you had yours with a one so I used ones. If they were supposed to be the letter I instead, sorry about that.