# Payroll tax calculation

• ### Question

•      Please someone help me in doing the following Payroll tax calculation. What I need is a VBA code for MS Access 2013. I am a complete novice and would appreciate if the code is copy and paste. Thanks in advance for any help or suggestion. SALARY RANGE TAX RATE EXEMPTIONS \$0 - \$600 0% 0 \$601 - \$1650 10% 150 \$1651 - \$3200 15% 200 \$3201 - \$5250 20% 350 \$5251 - \$7800 25% 500 \$7801 - \$10900 30% 1000 \$10901 - ABOVE 35% 1500

Wednesday, February 8, 2017 8:44 AM

• Hi,

You could use Select...Case Statement (Visual Basic) to calculate how may tax should be, then return the result.

E.g.

A form with salary field and cal field. Use the following code to update the cal field.

```Private Sub salary_AfterUpdate()
Dim sal As Integer
Dim tax As Integer
sal = salary.Value
Select Case sal
Case 0 To 600
tax = 0
Case 601 To 1650
tax = (sal - 600) * 0.1
Case 1651 To 3200
tax = (sal - 1650) * 0.15 + 150
End Select
cal.Value = sal - tax
End Sub```

Regards,

Celeste

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Marked as answer by Monday, February 13, 2017 12:30 PM
Friday, February 10, 2017 8:15 AM

### All replies

• Can you post more details?

Normally I would use a table to define the salaray range and the tax rate and exemption. Then you can use for example DLookup() to get the necessary value(s).

Wednesday, February 8, 2017 9:05 AM
• Hi,

I would suggest you add a calculated field to calculate expressions.

Regards,

Celeste

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Thursday, February 9, 2017 4:48 AM
• Thanks so much for your response. If possible what I was trying to do is since salary info is there when I enter hours worked access to calculate and give me net pay.

Thursday, February 9, 2017 8:17 AM
• Thank you Celeste for your reply. If possible what I was trying to do is since salary info is there when I enter hours worked access to calculate and give me net pay.

Thursday, February 9, 2017 8:19 AM
• Hi,

You could use Select...Case Statement (Visual Basic) to calculate how may tax should be, then return the result.

E.g.

A form with salary field and cal field. Use the following code to update the cal field.

```Private Sub salary_AfterUpdate()
Dim sal As Integer
Dim tax As Integer
sal = salary.Value
Select Case sal
Case 0 To 600
tax = 0
Case 601 To 1650
tax = (sal - 600) * 0.1
Case 1651 To 3200
tax = (sal - 1650) * 0.15 + 150
End Select
cal.Value = sal - tax
End Sub```

Regards,

Celeste

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Marked as answer by Monday, February 13, 2017 12:30 PM
Friday, February 10, 2017 8:15 AM
• The problem with a VBA approach is that data is being encoded in the object definition.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

The correct way to model this is, as Stefan pointed out earlier, to store the values in columns in a table.  You have almost shown the structure for such a table in your original post.  The only difference would be that the lower and upper salary values per tax band would be separate columns in the table, e.g.

TaxParameters
….SalaryFrom
….SalaryTo
….TaxRate
….Exemption

The tax due can then be computed in a query along these lines:

SELECT EmployeeID, GrossSalary,
(GrossSalary-Exemption)*TaxRate AS TaxDue,
FROM PayRoll INNER JOIN TaxParameters
ON (GrossSalary BETWEEN SalaryFrom AND SalaryTo);

PS:  The tax rates should be stored as fractional values, i,e 0.1, 0.15, 0.2 etc., and only formatted as percentages.

Ken Sheridan, Stafford, England

• Edited by Friday, February 10, 2017 11:24 PM Postscript added.
Friday, February 10, 2017 11:20 PM
• I agree with Ken S that this should be approached with a query rather than a VBA code set.  And like Stephan posted - need more info.

The max & min of your salary range need to be columns, not a text string in a single field.

I don't get how the exemptions and hours would all calculate together.  If you gave a couple examples of your calculation I don't think that query would be too difficult.

Saturday, February 11, 2017 4:39 PM
• Celeste thanks so much that is exactly what I want. You are a life saver. Thanks so so much.

Best regards,

Monday, February 13, 2017 12:31 PM
• Thanks for your reply. What I was trying to do is that since the salary data , the tax rate and the exemptions there by entering hours worked to get the net pay was my plan.

EmployeeID   Hours   Salary   TaxRate    Exmptions   NetPay

Monday, February 13, 2017 12:40 PM
• Thanks Ken appreciate your response.
Monday, February 13, 2017 12:40 PM