How do I populate this one?

[marcondes]

My client charges different commission rates to different dollar amounts. For example: If the amount is $500 or less there's a flat fee of $10. (I'm fine until there) From $501 to $600 the fee is $12, from $601 to $700 = $14, from $701 to $800 = $16, and so on.

This is what I have right now on the Control Source:

=IIf([DollarAmt]<501,IIf([DollarAmt]<1,0,10),IIf([DollarAmt]<601,12,IIf([DollarAmt]<701,14,IIf([DollarAmt]<801,16,IIf([DollarAmt]<901,18,IIf([DollarAmt]<1001,20,IIf([DollarAmt]<1101,22,IIf([DollarAmt]<1201,24,IIf([DollarAmt]<1301,26,IIf([DollarAmt]<1401,28,IIf([DollarAmt]<1501,30,[DollarAmt]*0.02)))))))))))

This IIf statement will do what I want up to $1500 and after that 2% of the amount. Any ideas on how to keep going with this formula?
Thanks

 

[RickSpr]

I'd suggest either of two options:
1. Replace the IIf with a call to a public function that takes the DollarAmt as a parameter and returns the commission amount.
2. Create a table with &quot;from&quot; and &quot;to&quot; dollar amounts and the commission for each. Use a DLookup() in the control source to retrieve the commission amount. Rick Sprague

 

[MichaelRed]

Public Function basCommis(AmtIn As Double) As Double

    'Michael Red    5/13/2002

    Dim MyAmt As Double

    MyAmt = Int(AmtIn / 100)

    If (MyAmt < 6) Then
        MyAmt = 5
    End If

    basCommis = MyAmt * 2

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over