help with code in a public function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The below function works but returns wrong calculation after Elseif
I am new to making my own function and have been at this for days


Option Compare Database
Public Function calculatecommission(totalsales As Currency) As Currency
' Take Total Sales and calculate commission
Dim curcommission As Currency

If totalsales <= 1000 Then
curcommission = Forms!Payroll.totalsales * Forms!Payroll.commrate1
ElseIf totalsales <= 2500 Then
curcommission = Forms!Payroll.totalsales * Forms!Payroll.commrate2 *
Forms!Payroll.commrate1 - 1000
Else
curcommission = Forms!Payroll.totalsales * Forms!Payroll.commrate2 *
Forms!Payroll.commrate3 - 2500
End If

' return calculated amount
calculatecommission = curcommission

End Function


commrate 1, 2, and 3 are commission percentages assigned in employee table.
the calculation works for sales amount <= 1000 but fails to calculate
properly after that Can anyone point me in the right direction?
Thank You .. Roger
 
Hi Roger,

Better to use a Select Case structure here. Maybe what you need is

Select Case totalsales
Case Is <= 1000
curcommission = Forms!Payroll.totalsales * Forms!Payroll.commrate1

Case Is <= 2500
curcommission = _
(Forms!Payroll.totalsales - 1000) * Forms!Payroll.commrate2 _
+ 1000 * Forms!Payroll.commrate1

Case Else
curcommission = _
(Forms!Payroll.totalsales - 2500) * Forms!Payroll.commrate3 _
+ 1500 * Forms!Payroll.commrate2 _
+ 1000 * Forms!Payroll.commrate1
End Select

Best practice is to store in your table not only the commission rates
but also the rules and thresholds for calculating the commision. That
way, when the rules or thresholds change next year or the year after,
you won't have to re-write your code.
 
John
Thank you, thank you, thank you.. you just made one old man's raniy satuday
morning great. Your soulotion was perfect..works like a charm. Thank you for
taking your valuable to answer my post.
Roger
 
Back
Top