Multiple calculations

[XeniaEagle]

I am trying to make an expression to calculate income tax witholding. I have
worked 14 hours now with no suscess. Here is what I am trying to do
Amount of Gross pay
Not over $51 Tax = $0
$51 to $188 10% in excess of $51
$188 to $606 $13.70 plus 15% in excess of $188
$606 to $1341 $76.40 plus 25% in excess of $606
Any help is appreciated. I can not find anything in my book or in help.
My head is getting sore from banging it on the wall.
I am putting this in a querie, where I have Gross pay as a field.
Thanks
Roger

 

[Edward G]

You may have to tweak this somewhat to make it legal since I have made some
presumptions where the dollar amount overlap ie $51 to 188 vs $51.01 to 188,
but this should work otherwise.

Expr1: IIf([GrossPay]<=51,0) & IIf([GrossPay]>51 And
[GrossPay]<=188,[GrossPay]*0.1) & IIf([GrossPay]>188 And
[GrossPay]<=606,13.7+[GrossPay]*0.15) & IIf([GrossPay]>606 And
[GrossPay]<=1341,76.4+[GrossPay]*0.25)

 

[Randy Balbuena]

I am trying to make an expression to calculate income tax witholding. I
have
worked 14 hours now with no suscess. Here is what I am trying to do
Amount of Gross pay
Not over $51 Tax = $0
$51 to $188 10% in excess of $51
$188 to $606 $13.70 plus 15% in excess of $188
$606 to $1341 $76.40 plus 25% in excess of $606
Any help is appreciated. I can not find anything in my book or in help.
My head is getting sore from banging it on the wall.
I am putting this in a querie, where I have Gross pay as a field.
Thanks
Roger

XeniaEagle, put the function below in a module and save the module with name
"modTaxRules." In your query, create a new column, which field reads as
follow:

TaxWitheld: GetTaxWitheld(Income)

'-----------------------BEGIN MODTAXRULES-----------------------

Public Function GetTaxWitheld(Income As Currency) As Currency

Dim BaseFee As Currency 'base tax
Dim Excess As Currency
Dim ExcessFeePercent As Currency
Dim FeeForExcess As Currency

Select Case Income

Case Is <= 50
BaseFee = 0
Excess = 0
ExcessFeePercentage = 0

Case 51 To 187
BaseFee = 0
Excess = Income - 51
ExcessFeePercentage = 10

Case 188 To 605
BaseFee = 13.7
Excess = Income - 188
ExcessFeePercentage = 15

Case 606 To 1340
BaseFee = 76.4
Excess = Income - 606
ExcessFeePercentage = 25

Case Is >= 1341
BaseFee = 240
Excess = Income - 1341
ExcessFeePercentage = 40
End Select

'The excess cannot be negative. If it is you have made a mistake.
'Make sure the last number in the Excess subtraction matches the
'first number in the "case" it belongs to. Check all the numbers
'on the select case above.

If Excess < 0 Then Excess = 0

'Applies the percentage to the excess
FeeForExcess = (Excess / 100) * ExcessFeePercentage

'Return total fees
GetTaxWitheld = BaseFee + FeeForExcess

End Function

'-----------------------BEGIN MODTAXRULES-----------------------

Bang your head no more. Hope it helps.

 

[John Vinson]

I am trying to make an expression to calculate income tax witholding. I have
worked 14 hours now with no suscess. Here is what I am trying to do
Amount of Gross pay
Not over $51 Tax = $0
$51 to $188 10% in excess of $51
$188 to $606 $13.70 plus 15% in excess of $188
$606 to $1341 $76.40 plus 25% in excess of $606
Any help is appreciated. I can not find anything in my book or in help.
My head is getting sore from banging it on the wall.
I am putting this in a querie, where I have Gross pay as a field.
Thanks
Roger

The Switch function is handy for this kind of thing. It takes
arguments in pairs; if the first element of a pair evaluates to TRUE,
it returns the second member of the pair; if not it goes to the next
pair. So:

Withold: Switch([Gross] < 51, 0, [Gross] < 188, ([Gross] - 188) * .10,
[Gross] < 606, 13.70 + ([Gross] - 606) * .15, [Gross] < 1341, 76.4 +
([Gross] - 606) * .25, True, <whatever the witholding is for over
$1341)


John W. Vinson[MVP]

 

[XeniaEagle]

Thanks
Edward, Randy & John
I'll try all 3
i was close, but couldn't get it to work
Thanks Again
Roger

I am trying to make an expression to calculate income tax witholding. I have
worked 14 hours now with no suscess. Here is what I am trying to do
Amount of Gross pay
Not over $51 Tax = $0
$51 to $188 10% in excess of $51
$188 to $606 $13.70 plus 15% in excess of $188
$606 to $1341 $76.40 plus 25% in excess of $606
Any help is appreciated. I can not find anything in my book or in help.
My head is getting sore from banging it on the wall.
I am putting this in a querie, where I have Gross pay as a field.
Thanks
Roger

The Switch function is handy for this kind of thing. It takes
arguments in pairs; if the first element of a pair evaluates to TRUE,
it returns the second member of the pair; if not it goes to the next
pair. So:

Withold: Switch([Gross] < 51, 0, [Gross] < 188, ([Gross] - 188) * .10,
[Gross] < 606, 13.70 + ([Gross] - 606) * .15, [Gross] < 1341, 76.4 +
([Gross] - 606) * .25, True, <whatever the witholding is for over
$1341)


John W. Vinson[MVP]

 

[Edward G]

Xenia,

Looks like I completely misunderstood the significance of "in excess of" in
your post.
Correction follows (more for my own edification than yours):

Expr1: CCur(IIf([GrossPay]<=51,0) & IIf([GrossPay]>51 And
[GrossPay]<=188,([GrossPay]-51)*0.1) & IIf([GrossPay]>188 And
[GrossPay]<=606,13.7+([GrossPay]-188)*0.15) & IIf([GrossPay]>606 And
[GrossPay]<=1341,76.4+([GrossPay]-606)*0.25))

Sorry bout that.

Ed

Thanks
Edward, Randy & John
I'll try all 3
i was close, but couldn't get it to work
Thanks Again
Roger

I am trying to make an expression to calculate income tax witholding. I have
worked 14 hours now with no suscess. Here is what I am trying to do
Amount of Gross pay
Not over $51 Tax = $0
$51 to $188 10% in excess of $51
$188 to $606 $13.70 plus 15% in excess of $188
$606 to $1341 $76.40 plus 25% in excess of $606
Any help is appreciated. I can not find anything in my book or in help.
My head is getting sore from banging it on the wall.
I am putting this in a querie, where I have Gross pay as a field.
Thanks
Roger

The Switch function is handy for this kind of thing. It takes
arguments in pairs; if the first element of a pair evaluates to TRUE,
it returns the second member of the pair; if not it goes to the next
pair. So:

Withold: Switch([Gross] < 51, 0, [Gross] < 188, ([Gross] - 188) * .10,
[Gross] < 606, 13.70 + ([Gross] - 606) * .15, [Gross] < 1341, 76.4 +
([Gross] - 606) * .25, True, <whatever the witholding is for over
$1341)


John W. Vinson[MVP]

 

[XeniaEagle]

Thanks
Edward

I wound up using John's idea. I used the switch function.
Now I have another problem. It will not accept the Currency format.
I have some percents for state tax which are .0808
the return calculation is with the 4 decimal places, and will
put it in the currency format.
Any idea's what is wrong?

Thanks
Roger

Xenia,

Looks like I completely misunderstood the significance of "in excess of" in
your post.
Correction follows (more for my own edification than yours):

Expr1: CCur(IIf([GrossPay]<=51,0) & IIf([GrossPay]>51 And
[GrossPay]<=188,([GrossPay]-51)*0.1) & IIf([GrossPay]>188 And
[GrossPay]<=606,13.7+([GrossPay]-188)*0.15) & IIf([GrossPay]>606 And
[GrossPay]<=1341,76.4+([GrossPay]-606)*0.25))

Sorry bout that.

Ed

Thanks
Edward, Randy & John
I'll try all 3
i was close, but couldn't get it to work
Thanks Again
Roger

On Wed, 22 Dec 2004 17:45:03 -0800, "XeniaEagle"

I am trying to make an expression to calculate income tax witholding. I have
worked 14 hours now with no suscess. Here is what I am trying to do
Amount of Gross pay
Not over $51 Tax = $0
$51 to $188 10% in excess of $51
$188 to $606 $13.70 plus 15% in excess of $188
$606 to $1341 $76.40 plus 25% in excess of $606
Any help is appreciated. I can not find anything in my book or in help.
My head is getting sore from banging it on the wall.
I am putting this in a querie, where I have Gross pay as a field.
Thanks
Roger

The Switch function is handy for this kind of thing. It takes
arguments in pairs; if the first element of a pair evaluates to TRUE,
it returns the second member of the pair; if not it goes to the next
pair. So:

Withold: Switch([Gross] < 51, 0, [Gross] < 188, ([Gross] - 188) * .10,
[Gross] < 606, 13.70 + ([Gross] - 606) * .15, [Gross] < 1341, 76.4 +
([Gross] - 606) * .25, True, <whatever the witholding is for over
$1341)


John W. Vinson[MVP]

 

[Edward G]

Hmmm. You right clicked on the column in design view, selected
Properties>Format and it won't accept
currency?????????? Is that right? BTW, on values less than $606 is John's
code working okay?

Ed

Thanks
Edward

I wound up using John's idea. I used the switch function.
Now I have another problem. It will not accept the Currency format.
I have some percents for state tax which are .0808
the return calculation is with the 4 decimal places, and will
put it in the currency format.
Any idea's what is wrong?

Thanks
Roger

Xenia,

Looks like I completely misunderstood the significance of "in excess of" in
your post.
Correction follows (more for my own edification than yours):

Expr1: CCur(IIf([GrossPay]<=51,0) & IIf([GrossPay]>51 And
[GrossPay]<=188,([GrossPay]-51)*0.1) & IIf([GrossPay]>188 And
[GrossPay]<=606,13.7+([GrossPay]-188)*0.15) & IIf([GrossPay]>606 And
[GrossPay]<=1341,76.4+([GrossPay]-606)*0.25))

Sorry bout that.

Ed

Thanks
Edward, Randy & John
I'll try all 3
i was close, but couldn't get it to work
Thanks Again
Roger

:

On Wed, 22 Dec 2004 17:45:03 -0800, "XeniaEagle"

I am trying to make an expression to calculate income tax

witholding. I
have

worked 14 hours now with no suscess. Here is what I am trying to do
Amount of Gross pay
Not over $51 Tax = $0
$51 to $188 10% in excess of $51
$188 to $606 $13.70 plus 15% in excess of $188
$606 to $1341 $76.40 plus 25% in excess of $606
Any help is appreciated. I can not find anything in my book or in help.
My head is getting sore from banging it on the wall.
I am putting this in a querie, where I have Gross pay as a field.
Thanks
Roger

The Switch function is handy for this kind of thing. It takes
arguments in pairs; if the first element of a pair evaluates to TRUE,
it returns the second member of the pair; if not it goes to the next
pair. So:

Withold: Switch([Gross] < 51, 0, [Gross] < 188, ([Gross] - 188) * ..10,
[Gross] < 606, 13.70 + ([Gross] - 606) * .15, [Gross] < 1341, 76.4 +
([Gross] - 606) * .25, True, <whatever the witholding is for over
$1341)


John W. Vinson[MVP]

 

[XeniaEagle]

Edward

John's post is working perfectly, except it returns 4 decimal places.
The field will not format as currency.
I made a new field and multiplied the old field by 1 and formated as
currency, that is working. I could not get anything else to work.
That is not the correct way of doing it, but don't know what else to do.

Thanks
Roger

Hmmm. You right clicked on the column in design view, selected
Properties>Format and it won't accept
currency?????????? Is that right? BTW, on values less than $606 is John's
code working okay?

Ed

Thanks
Edward

I wound up using John's idea. I used the switch function.
Now I have another problem. It will not accept the Currency format.
I have some percents for state tax which are .0808
the return calculation is with the 4 decimal places, and will
put it in the currency format.
Any idea's what is wrong?

Thanks
Roger

Xenia,

Looks like I completely misunderstood the significance of "in excess of" in
your post.
Correction follows (more for my own edification than yours):

Expr1: CCur(IIf([GrossPay]<=51,0) & IIf([GrossPay]>51 And
[GrossPay]<=188,([GrossPay]-51)*0.1) & IIf([GrossPay]>188 And
[GrossPay]<=606,13.7+([GrossPay]-188)*0.15) & IIf([GrossPay]>606 And
[GrossPay]<=1341,76.4+([GrossPay]-606)*0.25))

Sorry bout that.

Ed


Thanks
Edward, Randy & John
I'll try all 3
i was close, but couldn't get it to work
Thanks Again
Roger

:

On Wed, 22 Dec 2004 17:45:03 -0800, "XeniaEagle"

I am trying to make an expression to calculate income tax witholding. I
have
worked 14 hours now with no suscess. Here is what I am trying to do
Amount of Gross pay
Not over $51 Tax = $0
$51 to $188 10% in excess of $51
$188 to $606 $13.70 plus 15% in excess of $188
$606 to $1341 $76.40 plus 25% in excess of $606
Any help is appreciated. I can not find anything in my book or in
help.
My head is getting sore from banging it on the wall.
I am putting this in a querie, where I have Gross pay as a field.
Thanks
Roger

The Switch function is handy for this kind of thing. It takes
arguments in pairs; if the first element of a pair evaluates to TRUE,
it returns the second member of the pair; if not it goes to the next
pair. So:

Withold: Switch([Gross] < 51, 0, [Gross] < 188, ([Gross] - 188) * ..10,
[Gross] < 606, 13.70 + ([Gross] - 606) * .15, [Gross] < 1341, 76.4 +
([Gross] - 606) * .25, True, <whatever the witholding is for over
$1341)


John W. Vinson[MVP]

 

[John Vinson]

John's post is working perfectly, except it returns 4 decimal places.
The field will not format as currency.

I'd suggest that you wrap the Switch() expression in two other
functions:

Round(CCur(Switch(....)), 2)

to convert the String value returned by Switch to a currency value,
and then round it to two decimals.

John W. Vinson[MVP]

 

[XeniaEagle]

Thanks
John

I'll try that tomorrow.
Been a long day.

Thanks
Roger

 

[XeniaEagle]

One more question
I have been thinking of different senerios, that can come up
I think I have them all covered, but one.
I have hourly pay rate in a field.
If that rate should change during the year,
How can I change all future payroll and
leave all old ones unchanged?

Thanks
Roger

 

[Edward G]

I think what you should have is an Employee table that contains things like
the employee's name,address, phone#, ss#, etc and a separate table called
HourlyRates joined to Employee by EmployeeID. The Hourly Rates table should
have an EffectiveDate field. This would allow you to calculate payroll past
and present on the fly. I should confess here that my experience with
databases is mostly from an Inventory, Purchasing, and Production Control
perspective. I was honor bound not to look at payroll tables in our
database. Please drop me an email. I want to ask you a question.

 

[XeniaEagle]

Hi Edward

I tried to e mail you, but it came back
My e mail is (e-mail address removed)
E mail me with your question.

Thanks Roger