Code for calculating employees overtime pay

[orange]

I have looked at your info and have tried to work through NIS and PAYE calculations.


I focused on natregno 5703221467.
I have the code to get the NIS and Paye

For NatRegNo (JNIS)5703221467   Emp_Code E  Calculated NIS is ... 21.87
Calculated PAYE is ... 128.75

But as I got into the Transaction SubForm I realized that, although you and I were discussing PAYE, the subform is dealing with Tax. The PAYE column heading is using the Tax variable. It also deals with monthly rates etc which we hadn't discussed.

In my tests, I set up functions for NIS and PAYE calculations. The functions are JNIS and JPaye.
These functions are

'---------------------------------------------------------------------------------------
' Procedure : JNis
' Author    : Jack
' Created   : 4/30/2011
' Purpose   : Calculations based on posts. NatRegNo 5703221467.
' Used this function to separate any extraneous info
' NOTE:  There may be other conditions involved!!!!!!!
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function JNis( _
 strNatRegNo As String _
, CurSalary As Currency _
, curDutyAllowance As Currency _
, curNISLimit As Currency _
, NumHours As Currency _
, curRate As Currency _
, emp_code As String _
, NISrateP As Currency _
, NISRateT As Currency) As Currency

'Note:
'DutyAllowance is Allowance from Rates Table for this Employee's rank
On Error GoTo JNis_Error
Dim curnis As Currency

' no NIS if salary >=NisLimit
If CurSalary >= curNISLimit Then
    curnis = 0
End If
'
If CurSalary + curDutyAllowance < curNISLimit Then
    curnis = (curNISLimit - (CurSalary + curDutyAllowance)) * IIf(emp_code = "E", 0.088, 0.101)
End If

'Set the return value to 2 decimal places using Round function
JNis = Round(curnis, 2)
Debug.Print "For NatRegNo (JNIS)" & strNatRegNo & "   Emp_Code " & emp_code; "  Calculated NIS is ... " & JNis
   On Error GoTo 0
   Exit Function

JNis_Error:

    MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure JNis of Module Module1"
End Function
'---------------------------------------------------------------------------------------
' Procedure : jPaye
' Author    : Jack
' Created   : 4/30/2011
' Purpose   : Attempt to calculate PAYE based on posts on AccessForums.net May 4/2011
'
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function jPaye(curPayeLimit As Currency, _
 strNatRegNo As String, _
 CurSalary As Currency, _
 NumHours As Currency, _
 curRate As Currency, _
 CurAllowances As Currency, _
 Paye1 As Currency, _
 Paye2 As Currency) As Currency
 

'Note CurAllowances is  Sum of allowances in Allowance Table for this employee
Dim CurGrossOvertimePay As Currency
Dim curPayeTotal As Currency
Dim curPaye1 As Currency
Dim curPaye2 As Currency
'Dim Paye1 As Currency
'Dim Paye2 As Currency
On Error GoTo jPaye_Error
CurAllowances = 80 + 114.43 'Duty allowance +Washing allowance
CurGrossOvertimePay = NumHours * curRate
If (CurSalary + CurGrossOvertimePay + CurAllowances) >= curPayeLimit Then
curPaye2 = ((CurSalary + CurGrossOvertimePay + CurAllowances) - (curPayeLimit)) * Paye2
curPaye1 = ((curPayeLimit - (CurSalary + CurAllowances)) * Paye1)
curPayeTotal = Round(curPaye1 + curPaye2, 2)
Else
'
' it is not clear what the true expression is for this else condition
curPayeTotal = CurGrossOvertimePay * Paye1
End If
Debug.Print "Calculated PAYE is ... " & curPayeTotal

   On Error GoTo 0
   Exit Function

jPaye_Error:

    MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure jPaye of Module Module1"
End Function

I don't think all conditions and expressions are represented. I got confused again when I found the Transaction subform afterUpdate code was dealing with Tax and annual/month terms and not PAYE as had been discussed.
The outputs (from my Functions) for the other natRegNo was as follows

For NatRegNo (JNIS)5409120011 Emp_Code E Calculated NIS is ... 0
Calculated PAYE is ... 167.4
For NatRegNo (JNIS)5703221467 Emp_Code E Calculated NIS is ... 21.87
Calculated PAYE is ... 128.75
For NatRegNo (JNIS)5802111271 Emp_Code E Calculated NIS is ... 0
Calculated PAYE is ... 121.14
For NatRegNo (JNIS)5909301111 Emp_Code T Calculated NIS is ... 174.85
Calculated PAYE is ... 34.174
For NatRegNo (JNIS)5811075001 Emp_Code E Calculated NIS is ... 76.83
Calculated PAYE is ... 363.97

I hope some of this is helpful.

A caution for you is that the Allowances table has Duty and Washing Allowances.
The Rate table also has Duty Allowance. This is confusing and will be a maintenance headache.

NIS calculations involve Rate table, where as PAYE uses the Allowances Table (TotalAll) in the Salary Info Query.

As stated previously, the Transaction subform has a heading PAYE (which we have focused on), but the underlying data is based on tax calculations (me.tax etc) in the code behind the form.

[Nixx1401]

Thank you very much I am very grateful for the assistance received.

This will certainly help

[orange]

You are welcome. If you have further questions, just post.
Good luck with your project.