Code:
Public Function CalcTax(pGross As Currency) As Currency
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim sSQL As String
Dim tmpGross As Currency
Const MinWage As Currency = 2166.68 ' <= hard coded from tax table
Set d = CurrentDb
CalcTax = 0
tmpGross = Int((pGross * 100) + 0.5) / 100
If tmpGross >= MinWage Then
sSQL = "SELECT [TaxAmount] FROM IncomeTaxMonthly WHERE " & tmpGross & " Between PayRangeLow And PayRangeHigh"
Set r = d.OpenRecordset(sSQL)
If Not r.EOF Then
CalcTax = r("TaxAmount")
End If
End If
On Error Resume Next
r.Close
Set r = Nothing
Set d = Nothing
End Function
In the query, the tax column would look like this:
Code:
Taxes: CalcTax([GROSS])
This executes in about < 5 seconds for approx 1042 records.
Possible Modification:
Instead of hard coding the min wage, you could run code on startup to initalize a glogal variable, then use that instead of the constant in the code.
Windows 7 32bit 
Access 2007
Reply With Quote
Originally Posted by chavez_sea
After a long night, I think I finally figured it out.
Where do I put the code, would that be in the SQL in the query? Also I would need the code to read the gross from the Payroll Table as they were calculated for the pay period. Really I cant write code, all my simple querries are recorded when I do my query design. EVen this tax code that I currently have, I got help sometime back from this forum. There are usually about 90-105 employees at any one pay period.
