If i get it working, I'll change it by a penny.So what if the amount is 12760? Or greater than 25520? What is the correct tax?
If i get it working, I'll change it by a penny.So what if the amount is 12760? Or greater than 25520? What is the correct tax?
I'm doing it in a queryChange the field names to fit according to your pdf you posted in PostSQL:SELECT e.EmployeeID, e.[Total of Pay Check], e.[Salary Pay Period], t.Percent FROM tblEmployees e INNER JOIN tblpayrolltaxes t ON e.[Filing Status] = t.[Filing Status] AND e.[Total of Pay Check] BETWEEN t.[Minimum Inc] AND t.[Maximum Inc]
SELECT
e.EmployeeID,
e.[Total of Pay Check],
e.[Salary Pay Period],
e.[Filing Status],
t.Percent
FROM tblEmployees e
INNER JOIN tblpayrolltaxes t
ON e.[Filing Status] = t.[Filing Status]
AND e.[Salary Pay Period] = t.[Payroll Period]
AND e.[Total of Pay Check] >= t.[Minimum Inc]
AND e.[Total of Pay Check] < t.[Maximum Inc]
This gave me errors also.SQL:SELECT e.EmployeeID, e.[Total of Pay Check], e.[Salary Pay Period], e.[Filing Status], t.Percent FROM tblEmployees e INNER JOIN tblpayrolltaxes t ON e.[Filing Status] = t.[Filing Status] AND e.[Salary Pay Period] = t.[Payroll Period] AND e.[Total of Pay Check] >= t.[Minimum Inc] AND e.[Total of Pay Check] < t.[Maximum Inc]
The above is a query - based on what you posted in the pdf
What happens if you try it? (Note I adjusted it a bit from the version you quoted)
IIf([tblEmployees].[Basic Salary] Between 0 And 12760,
[tblEmployees].[Basic Salary])/([tblEmployees].[Salary Pay Period]),
IIf([tblEmployees]![Basic Salary] Between 12760 And 25520,
[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/([tblEmployees].[Salary Pay Period]),
IIf([tblEmployees]![Basic Salary] Between 25520 And 280950,
[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/([tblEmployees].[Salary Pay Period]),"")))
I tried it, I'm getting "The expression you entered contains invalid syntax, or you need to enclose your text in quotes". It highlights the comma before the 2nd IIf."Tax" column:
Code:IIf([tblEmployees].[Basic Salary] Between 0 And 12760, [tblEmployees].[Basic Salary])/([tblEmployees].[Salary Pay Period]), IIf([tblEmployees]![Basic Salary] Between 12760 And 25520, [tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/([tblEmployees].[Salary Pay Period]), IIf([tblEmployees]![Basic Salary] Between 25520 And 280950, [tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/([tblEmployees].[Salary Pay Period]),"")))
Noting some edge case overlap
Well it will as you are not using that as any criteria to reduce the records, just for a calculation.I corrected it to read, Taxes: IIf([tblEmployees]![Basic Salary] Between [tblpayrolltaxes]![Min] And [tblpayrolltaxes]![Max],[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Min],0)
It is still calculating people out side of the Min and Max
Public Function CalcBonus(plngEmployeeID As Long, pdtStartDate As Variant, pdtEndDate As Variant, piBonusPeriod As Integer)
On Error GoTo ErrHandler
Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim strEmployee As String, strSQL As String
Dim curBonus As Currency
Dim blnMultiLevel As Boolean
Set db = CurrentDb()
strEmployee = DLookup("[Forename] & ' ' & [Surname]", "tblEmployee", "EmployeeID = " & plngEmployeeID)
SetStatusBar ("Calculating Bonus for " & strEmployee)
' Need to check if commission is multi level. if not plain math will do the trick
blnMultiLevel = Nz(DLookup("CommMultiLevel", "tblEmployee", "[EmployeeID] = " & plngEmployeeID), 0) ' No longer needed?
' BonusCount and
If IsNull(pdtStartDate) Then
Set qdf = CurrentDb.QueryDefs("qryBonusCommissionNull")
Else
Set qdf = CurrentDb.QueryDefs("qryBonusCommissionProcessed")
End If
With qdf
.Parameters("[tmpEmployeeID]") = plngEmployeeID
.Parameters("[tmpBonusPeriod]") = piBonusPeriod
If Not IsNull(pdtStartDate) Then
.Parameters("[tmpStartDate]") = pdtStartDate
.Parameters("[tmpEndDate]") = pdtEndDate
End If
End With
Set rst = qdf.OpenRecordset
If rst.EOF Then
SetStatusBar ("No Bonus Commission records found for " & strEmployee)
CalcBonus = 0
Exit Function
End If
rst.MoveFirst
Do Until rst.EOF
' Need to check how bonus is calculated?, by count or amount
Select Case rst.Fields("BonusType")
Case 2 ' Count
curBonus = Nz(DMax("BonusAmount", "qryEmployeeBonus", "employeeID=" & plngEmployeeID & " AND ProductID= " & rst!ProductID & " And Level <= " & rst!BonusCount & ""), 0)
Case 3 ' Amount
curBonus = Nz(DMax("BonusAmount", "qryEmployeeBonus", "employeeID=" & plngEmployeeID & " AND ProductID= " & rst!ProductID & " And LevelAmount <= " & rst!SumOfCommAmount & ""), 0)
End Select
CalcBonus = CalcBonus + curBonus
' Still need to keep track of bonus count
TempVars("BonusCount") = TempVars("BonusCount") + rst!BonusCount
rst.MoveNext
Loop
rst.Close
'Debug.Print "Bonus Count " & TempVars("BonusCount")
'Debug.Print "Bonus Value" & CalcBonus
ExitFunction:
Set db = Nothing
Set rst = Nothing
Set qdf = Nothing
Err_Exit:
Exit Function
ErrHandler:
MsgBox "Error " & Err.Number & " " & Err.Description
Resume ExitFunction
End Function
Tax: IIf([tblEmployees].[Basic Salary] Between 0 And 12760;[tblEmployees].[Basic Salary]/[tblEmployees].[Salary Pay Period];IIf([tblEmployees]![Basic Salary] Between 12760 And 25520;([tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/[tblEmployees].[Salary Pay Period];IIf([tblEmployees]![Basic Salary] Between 25520 And 280950;([tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/[tblEmployees].[Salary Pay Period];"")))
Note the different separatorCode:Tax: IIf([tblEmployees].[Basic Salary] Between 0 And 12760;[tblEmployees].[Basic Salary]/[tblEmployees].[Salary Pay Period];IIf([tblEmployees]![Basic Salary] Between 12760 And 25520;([tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/[tblEmployees].[Salary Pay Period];IIf([tblEmployees]![Basic Salary] Between 25520 And 280950;([tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/[tblEmployees].[Salary Pay Period];"")))
I'm definitely not that advanced.I would write my own function to do this. Easier to debug and understand.
I did for something similar
Code:Public Function CalcBonus(plngEmployeeID As Long, pdtStartDate As Variant, pdtEndDate As Variant, piBonusPeriod As Integer) On Error GoTo ErrHandler Dim db As Database Dim rst As Recordset Dim qdf As QueryDef Dim strEmployee As String, strSQL As String Dim curBonus As Currency Dim blnMultiLevel As Boolean Set db = CurrentDb() strEmployee = DLookup("[Forename] & ' ' & [Surname]", "tblEmployee", "EmployeeID = " & plngEmployeeID) SetStatusBar ("Calculating Bonus for " & strEmployee) ' Need to check if commission is multi level. if not plain math will do the trick blnMultiLevel = Nz(DLookup("CommMultiLevel", "tblEmployee", "[EmployeeID] = " & plngEmployeeID), 0) ' No longer needed? ' BonusCount and If IsNull(pdtStartDate) Then Set qdf = CurrentDb.QueryDefs("qryBonusCommissionNull") Else Set qdf = CurrentDb.QueryDefs("qryBonusCommissionProcessed") End If With qdf .Parameters("[tmpEmployeeID]") = plngEmployeeID .Parameters("[tmpBonusPeriod]") = piBonusPeriod If Not IsNull(pdtStartDate) Then .Parameters("[tmpStartDate]") = pdtStartDate .Parameters("[tmpEndDate]") = pdtEndDate End If End With Set rst = qdf.OpenRecordset If rst.EOF Then SetStatusBar ("No Bonus Commission records found for " & strEmployee) CalcBonus = 0 Exit Function End If rst.MoveFirst Do Until rst.EOF ' Need to check how bonus is calculated?, by count or amount Select Case rst.Fields("BonusType") Case 2 ' Count curBonus = Nz(DMax("BonusAmount", "qryEmployeeBonus", "employeeID=" & plngEmployeeID & " AND ProductID= " & rst!ProductID & " And Level <= " & rst!BonusCount & ""), 0) Case 3 ' Amount curBonus = Nz(DMax("BonusAmount", "qryEmployeeBonus", "employeeID=" & plngEmployeeID & " AND ProductID= " & rst!ProductID & " And LevelAmount <= " & rst!SumOfCommAmount & ""), 0) End Select CalcBonus = CalcBonus + curBonus ' Still need to keep track of bonus count TempVars("BonusCount") = TempVars("BonusCount") + rst!BonusCount rst.MoveNext Loop rst.Close 'Debug.Print "Bonus Count " & TempVars("BonusCount") 'Debug.Print "Bonus Value" & CalcBonus ExitFunction: Set db = Nothing Set rst = Nothing Set qdf = Nothing Err_Exit: Exit Function ErrHandler: MsgBox "Error " & Err.Number & " " & Err.Description Resume ExitFunction End Function
Yours would not be anywhere near that complicated.I'm definitely not that advanced.