Calculating sales tax

[krymer]

Hello,

I am using a generic invoice database that I downloaded and I need to set up
the invoice form to multiply by 8% sales tax. The current invoice has sales
tax of $10.00 no matter what amount is in the subtotal control source has in
it. The name of the current control source for the sales tax field is:
InvSalesTax is this by itself a formula? When I open the expression builder
all I see is InvSalesTax as the expression, this doesn't tell me what the
forumula is. What I did was under the After Update selection in the Property
Sheet box was I entered in this formula: =[InvSubtotal]*.08 I left the
control source as InvSalesTax and when I switch back to form view, I am still
getting a value of 10.00 I then changed the control source to blank
(nothing) and when i go to form view all I see is Unbound. I looked at the
field above sales tax, which is the subtotal and I don't see any formula
under After Update, it just has a control source with InvSubtotal, but I
don't see the actual formula, please help.

 

[Beetle]

InvSubTotal and InvSalesTax appear to be field names. The question is
are they fields in a table, or in a query? If they are fields in a table, then
you have a table design issue, as calculated values such as these should not
be stored in a table. If they are fields in a query, then they may be
appropriate, but you would need to open the query in design view in
order to see/modify the formulas for those fields.

Open the properties sheet for your *form* and look at the Record
Source property (on the Data tab). Is it a table or a query?

 

[Larry Linson]

Really? How would you know that, Steve?

There hasn't been nearly enough information passed to propose a solution.

"krymer" has a problem due to downloading something he/she doesn't
understand. You are compounding krymer's problem by recommending solutions
for a problem you couldn't possibly understand from the information given up
to now... unless, of course, you have inside information about the 'generic
database' being used.

Larry Linson

Add a textbox to your form for showing the sales tax. Do not bind this
textbox to any field i the recordsource of your form. Select the textbox,
open properties, go to the Data tab and type in the following for the
controlsource property:
=[InvSubtotal]*.08

Steve

Hello,

I am using a generic invoice database that I downloaded and I need to set
up
the invoice form to multiply by 8% sales tax. The current invoice has
sales
tax of $10.00 no matter what amount is in the subtotal control source has
in
it. The name of the current control source for the sales tax field is:
InvSalesTax is this by itself a formula? When I open the expression
builder
all I see is InvSalesTax as the expression, this doesn't tell me what the
forumula is. What I did was under the After Update selection in the
Property
Sheet box was I entered in this formula: =[InvSubtotal]*.08 I left the
control source as InvSalesTax and when I switch back to form view, I am
still
getting a value of 10.00 I then changed the control source to blank
(nothing) and when i go to form view all I see is Unbound. I looked at
the
field above sales tax, which is the subtotal and I don't see any formula
under After Update, it just has a control source with InvSubtotal, but I
don't see the actual formula, please help.

 

[krymer]

Ok, I was able to get the subtotal to multiply and get the sales tax amount
on the form. Under the control source for this item I typed in: =[InSubtotal]
*.08 and when I switch back to form view it appears to be calculating the
correct tax. I did take a look at the corresponding table, which does have a
field called: InvSalesTax and the amounts in this field were predetermined
depending on the product item, in other words there werent calculated. To
make things easier, I tried setting up an expression in the InvSalesTax field
in the table with the sales tax expression into the Validation Rule property
and when I go to save changes I get this error message: Data integrity rules
have been changed; existing data may not be valid for the new rules, do you
want to test the new rules? I clicked Yes, then I got: Invalid SQL syntax-
cannot use multiple columns in a column-level CHECK constraint. What can I
do about this?

 

[John... Visio MVP]

Stevie does not like to be encumbered with facts. The main purpose for his
post is not to answer a question, just to make people forget his pandering
posts.

John... Visio MVP

Really? How would you know that, Steve?

There hasn't been nearly enough information passed to propose a solution.

"krymer" has a problem due to downloading something he/she doesn't
understand. You are compounding krymer's problem by recommending solutions
for a problem you couldn't possibly understand from the information given
up to now... unless, of course, you have inside information about the
'generic database' being used.

Larry Linson

Add a textbox to your form for showing the sales tax. Do not bind this
textbox to any field i the recordsource of your form. Select the textbox,
open properties, go to the Data tab and type in the following for the
controlsource property:
=[InvSubtotal]*.08

Steve

 

[Mrs. Ugh]

You cannot put the calculation into the table that way. I would work in the
form that displays the information. In the After Update event of the
[InSubTotal] field, set the value of [InvSalesTax] to be = [InSubTotal] *
0.8. That way, each time the [InSubTotal] changes, your sales tax will be
recalculated. Repost if you don't know how to do this.

However, this will really only work if your table is a table of "orders" not
individual items, since Sales Tax is calculated on the total order not in
individual items and it doesn't come out correct if you add up 8% of each
item. Also I would store the sales tax rate in another table (with just one
record) and look it up so it is easier to change when the tax rate changes. I
know there is probably a better way to do the sales tax change, but this is
what works for me!
Jill

Ok, I was able to get the subtotal to multiply and get the sales tax amount
on the form. Under the control source for this item I typed in: =[InSubtotal]
*.08 and when I switch back to form view it appears to be calculating the
correct tax. I did take a look at the corresponding table, which does have a
field called: InvSalesTax and the amounts in this field were predetermined
depending on the product item, in other words there werent calculated. To
make things easier, I tried setting up an expression in the InvSalesTax field
in the table with the sales tax expression into the Validation Rule property
and when I go to save changes I get this error message: Data integrity rules
have been changed; existing data may not be valid for the new rules, do you
want to test the new rules? I clicked Yes, then I got: Invalid SQL syntax-
cannot use multiple columns in a column-level CHECK constraint. What can I
do about this?

Hello,

I am using a generic invoice database that I downloaded and I need to set up
the invoice form to multiply by 8% sales tax. The current invoice has sales
tax of $10.00 no matter what amount is in the subtotal control source has in
it. The name of the current control source for the sales tax field is:
InvSalesTax is this by itself a formula? When I open the expression builder
all I see is InvSalesTax as the expression, this doesn't tell me what the
forumula is. What I did was under the After Update selection in the Property
Sheet box was I entered in this formula: =[InvSubtotal]*.08 I left the
control source as InvSalesTax and when I switch back to form view, I am still
getting a value of 10.00 I then changed the control source to blank
(nothing) and when i go to form view all I see is Unbound. I looked at the
field above sales tax, which is the subtotal and I don't see any formula
under After Update, it just has a control source with InvSubtotal, but I
don't see the actual formula, please help.

 

[krymer]

Thanks for the help, I think I will just work in the form as you suggested. I
was also wondering if there is a way to keep track of all the sales tax that
have been applied to the invoices, so that when it comes time to send my
quarterly sales tax payments I have a place where they have been added up
from each invoice. Also if you have any suggestions on how I could apply
payments to customer accounts, so I know who has paid and if there is any
remaining balances left on each invoice. Thanks, Kevin

You cannot put the calculation into the table that way. I would work in the
form that displays the information. In the After Update event of the
[InSubTotal] field, set the value of [InvSalesTax] to be = [InSubTotal] *
0.8. That way, each time the [InSubTotal] changes, your sales tax will be
recalculated. Repost if you don't know how to do this.

However, this will really only work if your table is a table of "orders" not
individual items, since Sales Tax is calculated on the total order not in
individual items and it doesn't come out correct if you add up 8% of each
item. Also I would store the sales tax rate in another table (with just one
record) and look it up so it is easier to change when the tax rate changes. I
know there is probably a better way to do the sales tax change, but this is
what works for me!
Jill

Ok, I was able to get the subtotal to multiply and get the sales tax amount
on the form. Under the control source for this item I typed in: =[InSubtotal]
*.08 and when I switch back to form view it appears to be calculating the
correct tax. I did take a look at the corresponding table, which does have a
field called: InvSalesTax and the amounts in this field were predetermined
depending on the product item, in other words there werent calculated. To
make things easier, I tried setting up an expression in the InvSalesTax field
in the table with the sales tax expression into the Validation Rule property
and when I go to save changes I get this error message: Data integrity rules
have been changed; existing data may not be valid for the new rules, do you
want to test the new rules? I clicked Yes, then I got: Invalid SQL syntax-
cannot use multiple columns in a column-level CHECK constraint. What can I
do about this?

Hello,

I am using a generic invoice database that I downloaded and I need to set up
the invoice form to multiply by 8% sales tax. The current invoice has sales
tax of $10.00 no matter what amount is in the subtotal control source has in
it. The name of the current control source for the sales tax field is:
InvSalesTax is this by itself a formula? When I open the expression builder
all I see is InvSalesTax as the expression, this doesn't tell me what the
forumula is. What I did was under the After Update selection in the Property
Sheet box was I entered in this formula: =[InvSubtotal]*.08 I left the
control source as InvSalesTax and when I switch back to form view, I am still
getting a value of 10.00 I then changed the control source to blank
(nothing) and when i go to form view all I see is Unbound. I looked at the
field above sales tax, which is the subtotal and I don't see any formula
under After Update, it just has a control source with InvSubtotal, but I
don't see the actual formula, please help.

 

[Mrs. Ugh]

I have a "SalesTax" query. It uses the DSum function to add up all of the
sales taxes paid for the quarter specified. I use input parameters that ask
the user which quarter and year they want to calculate. I don't store the
info, but it is probably a good idea to have a Sales Tax paid table that
records the calculation each quarter.

For each order, I have an AmountPaid field where I enter how much the
customer paid. This works because I never have partial payments or account
balances. You can create a table that contains Customer Payment information.
I would have fields such as the PaymentID (autonumber unique identifier),
customerID (linking back to a customer table), DatePaid, AmountPaid,
PaymentMethod (cash, credit card, etc.). Then you can create a query (similar
to the Sales Tax query that adds up all of the customer's bills, all of their
payments, and calculates the difference.

This is just the back stuff; always use a form to display/edit the
data/information! On the form you can add command buttons that have preset
data filters such as "view outstanding balances". If you have a date due, you
could show overdue balances, etc. Create reports to show a list of all
outstanding balances or whatever.
Jill

Thanks for the help, I think I will just work in the form as you suggested. I
was also wondering if there is a way to keep track of all the sales tax that
have been applied to the invoices, so that when it comes time to send my
quarterly sales tax payments I have a place where they have been added up
from each invoice. Also if you have any suggestions on how I could apply
payments to customer accounts, so I know who has paid and if there is any
remaining balances left on each invoice. Thanks, Kevin

You cannot put the calculation into the table that way. I would work in the
form that displays the information. In the After Update event of the
[InSubTotal] field, set the value of [InvSalesTax] to be = [InSubTotal] *
0.8. That way, each time the [InSubTotal] changes, your sales tax will be
recalculated. Repost if you don't know how to do this.

However, this will really only work if your table is a table of "orders" not
individual items, since Sales Tax is calculated on the total order not in
individual items and it doesn't come out correct if you add up 8% of each
item. Also I would store the sales tax rate in another table (with just one
record) and look it up so it is easier to change when the tax rate changes. I
know there is probably a better way to do the sales tax change, but this is
what works for me!
Jill

Ok, I was able to get the subtotal to multiply and get the sales tax amount
on the form. Under the control source for this item I typed in: =[InSubtotal]
*.08 and when I switch back to form view it appears to be calculating the
correct tax. I did take a look at the corresponding table, which does have a
field called: InvSalesTax and the amounts in this field were predetermined
depending on the product item, in other words there werent calculated. To
make things easier, I tried setting up an expression in the InvSalesTax field
in the table with the sales tax expression into the Validation Rule property
and when I go to save changes I get this error message: Data integrity rules
have been changed; existing data may not be valid for the new rules, do you
want to test the new rules? I clicked Yes, then I got: Invalid SQL syntax-
cannot use multiple columns in a column-level CHECK constraint. What can I
do about this?

:

Hello,

I am using a generic invoice database that I downloaded and I need to set up
the invoice form to multiply by 8% sales tax. The current invoice has sales
tax of $10.00 no matter what amount is in the subtotal control source has in
it. The name of the current control source for the sales tax field is:
InvSalesTax is this by itself a formula? When I open the expression builder
all I see is InvSalesTax as the expression, this doesn't tell me what the
forumula is. What I did was under the After Update selection in the Property
Sheet box was I entered in this formula: =[InvSubtotal]*.08 I left the
control source as InvSalesTax and when I switch back to form view, I am still
getting a value of 10.00 I then changed the control source to blank
(nothing) and when i go to form view all I see is Unbound. I looked at the
field above sales tax, which is the subtotal and I don't see any formula
under After Update, it just has a control source with InvSubtotal, but I
don't see the actual formula, please help.

 

[John Spencer]

Be careful. Most states round up the amount of tax due to the next higher
penny and some of them are really particular that you do so also. Other
states just round up based on some minimal portion (like round up anything
over 1/4 cent).


To round up to the next highest penny, you can use an expression like the
following. Replace dblNumber with the calculation you are currently using to
get the sales tax.

-Int(-dblNumber * 10^2/ 10 ^ 2

You can even adjust the rounding up to consider the 1/4 penny rule by
adjusting the number you are rounding by subtracting .0025 from the dblNumber
value before you do the rounding up.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County