Sliding Scale Calculation

[mrsbean]

Please advise on the best way to go about performing a calculation for commission earned on a sliding scale according to purchase price.

E.G. if the purchase price is between 0 and 500, commission is 5% ; if the price is between 501 and 999, commission is 5.5% and so on.

Also, is there a limit to the number of conditions you can have in a calculation like this? I have lots of price categories! Thanks

 

[Mile-O]

In a standalone module, make a function in VBA using a SELECT CASE structure:

Public Function CommissionRate(ByVal curPurchasePrice As Currency) As Single
    Select Case curPurchasePrice
        Case Is <= 500
            CommissionRate = 5
        Case Is <= 999
            CommissionRate = 5.5
        Case Is <= 1500
            CommissionRate = 6
        Case Else
            CommissionRate = 7.5
    End Select
End Function

And in you query you can use it like any other function.

 

[ecniv]

Or you can put it into a look up table (so the rates/change at amounts can be controlled by the customer instead of you releasing an new update whenever they change).

From the look up table its possible to use a query to get the information, or you can have a drop down on the page which has the amounts and rates, and some code so if the amount is changed, it checks the dropdown and puts in the appropriate rate.

Or you can, on saving, pull the rate and set it into the main record.

Just a couple of other options...


Vince

 

[mrsbean]

Thanks, now how do I write a calculation in the form/query that calculates the commission amount (PurchasePrice * CommissionRate)?

In a standalone module, make a function in VBA using a SELECT CASE structure:

Public Function CommissionRate(ByVal curPurchasePrice As Currency) As Single
    Select Case curPurchasePrice
        Case Is <= 500
            CommissionRate = 5
        Case Is <= 999
            CommissionRate = 5.5
        Case Is <= 1500
            CommissionRate = 6
        Case Else
            CommissionRate = 7.5
    End Select
End Function

And in you query you can use it like any other function.