Calculating Tax

[Guest]

I am a new user and could use a bit of help. I have a table used for
additional job expenses. The table has (among a few other fields) Quantity,
Cost, Tax and Total. The Tax field is set up as a Yes/No checkbox because
some items are taxed, and some are not. When a user checks the Tax box, I
would like to tack on 15% to the Cost (the percentage doesn't change: it will
always be 15%). I'm able to multiply Quantity*Cost for the Total field, but
how (and where) do I incorporate 15% if Tax=Yes?

Any help is appreciated. A formula, code, instruction or website would help
a lot.
Thanks

 

[Douglas J. Steele]

Try something like

[Quantity]*[Cost]*IIf([Tax], 1.15, 1)

 

[Brendan Reynolds]

Hmph. Must be a homework exercise, as only in a completely academic exercise
will tax rates ever be fixed and unchanging. I would strongly suggest that
you not do it this way at all, but add a field for tax rate. (For items that
are not taxed, the rate will be zero). You can then calculate the tax easily
in, for example a query, with an expression such as [NetAmount] * [TaxRate].
The Yes/No field plus hard-coded tax rate approach, in addition to being
unrealistic, also requires a more complex formula: IIf([Tax] <> 0,
[NetAmount] * .15, 0).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Thanks for the responses.
I plugged in Mr. Steele's formula and it works great. It's exactly what I
was looking for! In response to Mr Reynolds' thread: I guess "Tax" was not
the correct term to use. Instead, I've changed the field name to Surcharge,
as we tack on 15% to any additional expenses that our technicians incur.
However, if I need to change this in the future, I've saved the thread for
reference.
Thanks again!

Hmph. Must be a homework exercise, as only in a completely academic exercise
will tax rates ever be fixed and unchanging. I would strongly suggest that
you not do it this way at all, but add a field for tax rate. (For items that
are not taxed, the rate will be zero). You can then calculate the tax easily
in, for example a query, with an expression such as [NetAmount] * [TaxRate].
The Yes/No field plus hard-coded tax rate approach, in addition to being
unrealistic, also requires a more complex formula: IIf([Tax] <> 0,
[NetAmount] * .15, 0).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I am a new user and could use a bit of help. I have a table used for
additional job expenses. The table has (among a few other fields)
Quantity,
Cost, Tax and Total. The Tax field is set up as a Yes/No checkbox because
some items are taxed, and some are not. When a user checks the Tax box, I
would like to tack on 15% to the Cost (the percentage doesn't change: it
will
always be 15%). I'm able to multiply Quantity*Cost for the Total field,
but
how (and where) do I incorporate 15% if Tax=Yes?

Any help is appreciated. A formula, code, instruction or website would
help
a lot.
Thanks

 

[Mike Painter]

Hmph. Must be a homework exercise, as only in a completely academic
exercise will tax rates ever be fixed and unchanging. I would
strongly suggest that you not do it this way at all, but add a field
for tax rate. (For items that are not taxed, the rate will be zero).
You can then calculate the tax easily in, for example a query, with
an expression such as [NetAmount] * [TaxRate]. The Yes/No field plus
hard-coded tax rate approach, in addition to being unrealistic, also
requires a more complex formula: IIf([Tax] <> 0, [NetAmount] * .15,
0).

Hmph, hmph
Only in a homework exercise would the tax rate be a calculated amount in a
query.

When Joe Customer calls for a copy of his invoice from three days before the
tax change he is going to want to know why you charged him at the higher
rate OR remain silent if the rate dropped and he has not paid.

 

[Brendan Reynolds]

Read my post a little more carefully, Mike. I did not suggest that the
*rate* be calculated.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hmph. Must be a homework exercise, as only in a completely academic
exercise will tax rates ever be fixed and unchanging. I would
strongly suggest that you not do it this way at all, but add a field
for tax rate. (For items that are not taxed, the rate will be zero).
You can then calculate the tax easily in, for example a query, with
an expression such as [NetAmount] * [TaxRate]. The Yes/No field plus
hard-coded tax rate approach, in addition to being unrealistic, also
requires a more complex formula: IIf([Tax] <> 0, [NetAmount] * .15,
0).

Hmph, hmph
Only in a homework exercise would the tax rate be a calculated amount in a
query.

When Joe Customer calls for a copy of his invoice from three days before
the tax change he is going to want to know why you charged him at the
higher rate OR remain silent if the rate dropped and he has not paid.

 

[Mike Painter]

Exactly my point. You calculate the amount of tax.
If I print the invoice today when the rate is 7% and print it tomorrow when
it is 8% the customer will complain. If it changes from 8% to 7% the
customer will remain silent and I'll be out the diffference.

Read my post a little more carefully, Mike. I did not suggest that the
*rate* be calculated.

Hmph. Must be a homework exercise, as only in a completely academic
exercise will tax rates ever be fixed and unchanging. I would
strongly suggest that you not do it this way at all, but add a field
for tax rate. (For items that are not taxed, the rate will be zero).
You can then calculate the tax easily in, for example a query, with
an expression such as [NetAmount] * [TaxRate]. The Yes/No field plus
hard-coded tax rate approach, in addition to being unrealistic, also
requires a more complex formula: IIf([Tax] <> 0, [NetAmount] * .15,
0).

Hmph, hmph
Only in a homework exercise would the tax rate be a calculated
amount in a query.

When Joe Customer calls for a copy of his invoice from three days
before the tax change he is going to want to know why you charged
him at the higher rate OR remain silent if the rate dropped and he
has not paid.

 

[Brendan Reynolds]

I calculate the amount of tax using the rate that was in effect at the time
the transaction took place. If the rate at the time the transaction took
place is 7%, then 7% is the rate to be applied. What the rate might be
tomorrow is irrelevant, as far as this transaction is concerned.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Exactly my point. You calculate the amount of tax.
If I print the invoice today when the rate is 7% and print it tomorrow
when it is 8% the customer will complain. If it changes from 8% to 7% the
customer will remain silent and I'll be out the diffference.

Read my post a little more carefully, Mike. I did not suggest that the
*rate* be calculated.

Brendan Reynolds wrote:
Hmph. Must be a homework exercise, as only in a completely academic
exercise will tax rates ever be fixed and unchanging. I would
strongly suggest that you not do it this way at all, but add a field
for tax rate. (For items that are not taxed, the rate will be zero).
You can then calculate the tax easily in, for example a query, with
an expression such as [NetAmount] * [TaxRate]. The Yes/No field plus
hard-coded tax rate approach, in addition to being unrealistic, also
requires a more complex formula: IIf([Tax] <> 0, [NetAmount] * .15,
0).
Hmph, hmph
Only in a homework exercise would the tax rate be a calculated
amount in a query.

When Joe Customer calls for a copy of his invoice from three days
before the tax change he is going to want to know why you charged
him at the higher rate OR remain silent if the rate dropped and he
has not paid.

 

[Mike Painter]

I calculate the amount of tax using the rate that was in effect at
the time the transaction took place. If the rate at the time the
transaction took place is 7%, then 7% is the rate to be applied. What
the rate might be tomorrow is irrelevant, as far as this transaction
is concerned.

If you do this as a calculated value and the rate changes and customer
wants a second copy after the rate changes you *Will* have problems.
You either track the tax rates by date to keep Codd happy or you stuff the
current rate into your header record for future references.

Exactly my point. You calculate the amount of tax.
If I print the invoice today when the rate is 7% and print it
tomorrow when it is 8% the customer will complain. If it changes
from 8% to 7% the customer will remain silent and I'll be out the

Read my post a little more carefully, Mike. I did not suggest that
the *rate* be calculated.


Brendan Reynolds wrote:
Hmph. Must be a homework exercise, as only in a completely
academic exercise will tax rates ever be fixed and unchanging. I
would strongly suggest that you not do it this way at all, but
add a field for tax rate. (For items that are not taxed, the rate
will be zero). You can then calculate the tax easily in, for
example a query, with an expression such as [NetAmount] *
[TaxRate]. The Yes/No field plus hard-coded tax rate approach, in
addition to being unrealistic, also requires a more complex
formula: IIf([Tax] <> 0, [NetAmount] * .15, 0).
Hmph, hmph
Only in a homework exercise would the tax rate be a calculated
amount in a query.

When Joe Customer calls for a copy of his invoice from three days
before the tax change he is going to want to know why you charged
him at the higher rate OR remain silent if the rate dropped and he
has not paid.

 

[Brendan Reynolds]

I can only repeat, Mike, please read my post more carefully. You are
attacking something that is not there.
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I calculate the amount of tax using the rate that was in effect at
the time the transaction took place. If the rate at the time the
transaction took place is 7%, then 7% is the rate to be applied. What
the rate might be tomorrow is irrelevant, as far as this transaction
is concerned.

If you do this as a calculated value and the rate changes and customer
wants a second copy after the rate changes you *Will* have problems.
You either track the tax rates by date to keep Codd happy or you stuff the
current rate into your header record for future references.

Exactly my point. You calculate the amount of tax.
If I print the invoice today when the rate is 7% and print it
tomorrow when it is 8% the customer will complain. If it changes
from 8% to 7% the customer will remain silent and I'll be out the
diffference. Brendan Reynolds wrote:
Read my post a little more carefully, Mike. I did not suggest that
the *rate* be calculated.


Brendan Reynolds wrote:
Hmph. Must be a homework exercise, as only in a completely
academic exercise will tax rates ever be fixed and unchanging. I
would strongly suggest that you not do it this way at all, but
add a field for tax rate. (For items that are not taxed, the rate
will be zero). You can then calculate the tax easily in, for
example a query, with an expression such as [NetAmount] *
[TaxRate]. The Yes/No field plus hard-coded tax rate approach, in
addition to being unrealistic, also requires a more complex
formula: IIf([Tax] <> 0, [NetAmount] * .15, 0).
Hmph, hmph
Only in a homework exercise would the tax rate be a calculated
amount in a query.

When Joe Customer calls for a copy of his invoice from three days
before the tax change he is going to want to know why you charged
him at the higher rate OR remain silent if the rate dropped and he
has not paid.

 

[Brendan Reynolds]

It occurs to me that possibly we may be misunderstanding each other because
we are making different assumptions based on our familiarity with different
tax regimes. Your comment about storing the current rate in a header record
may be the clue. I live and work in Ireland, an EU country, and in the EU
different rates of VAT may apply to different items on the same invoice.
Under this tax regime, it would not make sense to store the current rate in
a header record (which rate?). The current rate would be stored in the
detail record.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I calculate the amount of tax using the rate that was in effect at
the time the transaction took place. If the rate at the time the
transaction took place is 7%, then 7% is the rate to be applied. What
the rate might be tomorrow is irrelevant, as far as this transaction
is concerned.

If you do this as a calculated value and the rate changes and customer
wants a second copy after the rate changes you *Will* have problems.
You either track the tax rates by date to keep Codd happy or you stuff the
current rate into your header record for future references.

Exactly my point. You calculate the amount of tax.
If I print the invoice today when the rate is 7% and print it
tomorrow when it is 8% the customer will complain. If it changes
from 8% to 7% the customer will remain silent and I'll be out the
diffference. Brendan Reynolds wrote:
Read my post a little more carefully, Mike. I did not suggest that
the *rate* be calculated.


Brendan Reynolds wrote:
Hmph. Must be a homework exercise, as only in a completely
academic exercise will tax rates ever be fixed and unchanging. I
would strongly suggest that you not do it this way at all, but
add a field for tax rate. (For items that are not taxed, the rate
will be zero). You can then calculate the tax easily in, for
example a query, with an expression such as [NetAmount] *
[TaxRate]. The Yes/No field plus hard-coded tax rate approach, in
addition to being unrealistic, also requires a more complex
formula: IIf([Tax] <> 0, [NetAmount] * .15, 0).
Hmph, hmph
Only in a homework exercise would the tax rate be a calculated
amount in a query.

When Joe Customer calls for a copy of his invoice from three days
before the tax change he is going to want to know why you charged
him at the higher rate OR remain silent if the rate dropped and he
has not paid.