Need to changing a Tax Rate

[Guest]

I have created forms and report that adds up prices of parts as SUBTOTAL then
I have another text box with the formula of =Nz([SUBTOTAL])*.049 for the
current tax rate of 4.9%

How would I change the tax rate without affecting previous invoices using
the old tax rate? I have thought of some ways but wanted to bring it here if
there might be better ways.

1. Create a new database with no previous data
2. Make a new table with tax rates then relate it accordingly to the
invoice creation table, them make changes as needed to the forms and reports
and previous data
3. Change the invoice creation table and form to type a tax rate each time
for each invoice created, then change the reports and previous data
accordingly for the new table field

 

[Allen Browne]

Add a TaxRate field to your table.

This solution copes with all these scenarios:
a) retaining past data correctly when there is a change in tax rate;
b) items/customers that are exempt from tax;
c) sales in different areas where the tax rate is not the same.

1. Open the table in design view, and add a field named (say) TaxRate, type
Number. In the lower pane of table design, set these properties for the
field:
Field Size: Double
Format: Percent
Required Yes.
Save. Close.

2. To populate the existing records, create a query using this table.
Change it to an Update query (Update on Query menu.)
Access adds an update row to the grid.
Drag the new TaxRate field into the grid.
In the Update row, enter: 0.49
Run the query.
All existing rows now have this tax rate.
You can close the query without saving.

3. Create a query to act as the source for your form, so you can calculate
the tax on a per-row basis. This example assumes you have fields named
Quantity (number of items) and UnitPriceEx (price each without tax):
Tax: CCur(Nz([Quantity] * [UnitPriceEx] * [TaxRate],0))
Save the query.

4. Open your form in design view, and change its RecordSource to this query.
You can now sum the Tax field for all rows in your form.

Hope that helps.

 

[Jamie Collins]

set these properties for the
field:
Field Size: Double
Format: Percent

I do not recommend an inexact floating point data type for something as
exacting as a tax rate. I recommend the OP use DECIMAL, which is a
fixed point type, with a scale (decimal places) of 2 or 3.

Jamie.

--

 

[Guest]

Thank You Allen and Jamie for your help!