Calculating a bonus - IIf use?

[AdamO]

Hello,

I am struggling to write an expression.

I have a 12 month moving average amount which is already calculated. I now want to work out a bonus amount which will vary according to this moving avearge amount.

The bonus is:

Moving Increment Bonus %
Average
50000 20
1% per 1000
40000 10
1% per 1000
30000 0
1% per 1000
20000 0.9
2% per 1000
10000 0.7

I have tried using a IIf calculation as follows:

BonusAmt: IIf ([MovAve]<50000,20, IIf([MovAve]Between 49000 And 50000,19, IIf([MovAve]Between 48000 And 49000,18,IIf([MovAve]Between 47000 And 48000,17,IIf([MovAve]Between 46000 And 47000,16,IIf([MovAve]Between 45000 And 46000,15,IIf([MovAve]Between 44000 And 45000,14.......and so on.

The expression results in an error message because it is too long and complex.

Could someone provide some guidance on how I can achieve the desired result.

I am do struggle with code, so any examples would be appreciated.

Thank you.

 

[Mile-O]

Just one thing about your IIf() structure.

Look at what happens if your moving average is less than 50,000 - it returns 20, so how do you propose that it can go through the remainder of the tests?

You might find it easier to write a function that uses a SELECT CASE statement that you can call from the query.

 

[AdamO]

Thank you for your reply.

I can see the problem with IIf as I detailed. I could use Between, however I would still have the same problem with the expression being too long/complex.

Any further guidance would be appreciated.

 

[Mile-O]

Put this code in a module:

Public Function CalculateBonus(ByVal lngAverage As Long) As Integer

    On Error GoTo Err_CalculateBonus

    Select Case lngAverage
        Case Is >= 50000
            CalculateBonus = 20
        Case Is >= 49000 And lngAverage < 50000
            CalculateBonus = 19
        Case Is >= 48000 And lngAverage < 49000
            CalculateBonus = 18
        Case Else
            CalculateBonus = 0
    End Select
    
Exit_CalculateBonus:
    Exit Function
    
Err_CalculateBonus:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CalculateBonus

End Function

The logic of the function should be easy enough to understand and you can change this to meet your needs.

In your query, simply put this instead:

BonusAmt: CalculateBonus([MovAve])