tax problem

[fido77777]

i have this situation something like the following

a control on a form gets income value from a table

i want anther control should calculate the tax for each income value for each record

the tax rate changes constantly like:

income --------------------tax
1000 ---------------------0.5%
2000 ----------------------1%
3000 ---------------------1.5%

and so on

i thought about using if function or select case
to select the tax rate appropriate to each income value

but that means i write a 200 line function
is there an easy way to get it done?

 

[pbaldy]

If it's as simple as that, I'd probably tweak the table to have min/max fields for the income, so 3 fields:

0 - 1000 - .5%
1001 - 2000 - 1%

then it's a fairly easy lookup, and you can even join to that table in queries to get the value.

 

[petehilljnr]

I'd even take that a step further because, if I understand income tax (and I'm absolutely no expert), you need to sum up for each tax step.
e.g. If Income Value = 2500, then tax value = (0.5% * (1000-0)) + (1% * (2000 - 1000)) + (1.5% * (2500 - 2000)) = 22.5 (or 22.47 if you use the whole dollars in your tax brackets e.g. 1001-2000)

So - if you make a table like:

IncomeMin, IncomeMax, TaxRate, TaxMin
1,1000,0.005,0
1000,2000,0.010,5
2000,3000,0.015,15

Then - you can link this table into your query and calculate income tax by using:

IncomeTax: (([IncomeValue] - [IncomeMin]) * [TaxRate]) + [TaxMin]

WHERE [IncomeValue] > [IncomeMin] AND [IncomeValue] <= [IncomeMax]

Just an idea.

Pete

 

[petehilljnr]

Forgot to say - TaxMin would be the total amount of tax payable where IncomeValue = IncomeMin.

 

[fido77777]

i think it was my mistake that i am not clear enough

the problem is not in the tax part
it is only an example

i will try to be more clear

if i have invoice
if it says 1000 so the tax calculated = 1000*.5%
if it says 2000 so the tax calculated = 1000* 1%
if it says 3000 so the tax calculated = 1000*1.5%

and so on

i want
1- a control on a form to calculate the total for invoice (that part is done)
2-a control text4 on a form to contain the formula [invoice]*[tax]
3-the value of tax should be related to the value of the invoice total

something like:

if the invoice <1000
then
text4=invoice*.5%
else
if the invoice <2000
then
text4=invoice*1%


and so on
my question is

using if function like that will mean that i will write so many conditions

i want an easy way to get the right tax value for each invoice total


thank you

 

[fido77777]

If it's as simple as that, I'd probably tweak the table to have min/max fields for the income, so 3 fields:

0 - 1000 - .5%
1001 - 2000 - 1%

then it's a fairly easy lookup, and you can even join to that table in queries to get the value.

it so close to what i want really
i want it done on a control on form
no problem using one more table
but can u put an example please
to make it more clear for me
thank you for your help

 

[namliam]

Use PBaldy's suggestion, make a table with min/max/tax values and "DLookup" it.

 

[pbaldy]

And here is a good reference on the syntax:

http://www.mvps.org/access/general/gen0018.htm

You want 2 conditions, looking for the value where the min value is less than or equal to your amount and the max value is greater than or equal to your amount.

 

[MSAccessRookie]

Why wouldn't a simple formula like this work? It defines the tax Rate properly in groups of $1,000. Of course, when the Invoice gets over $200,000, the results might be a little funny.

TaxCalculated = ((Int((TheInvoice-1)/1000)+1) * 0.05)

 

[fido77777]

thank you a lot pbaldy

many thanks too for every one helped me here

i have created the new table with 3 columns: min, max and rate
and put the dlookup in the control i wanted
and it works really great

problem solved

my thoughs was about using a loop to solve the problem
something like Do.....while to find the right rate
if this can work too please tell me

 

[pbaldy]

You could use a loop, but it would be less efficient than the DLookup. If you wanted to use a recordset, it would be much faster to use an SQL statement with a WHERE clause similar to your DLookup criteria, so it only returned the single record.

 

[fido77777]

in fact your way is better
that would allow user to change the min and max and rate values
if he had to in the future

i was just wondering if the loop can get it done

thank you for your help