Ignoring the comments about your table structure and real-life data
requirements for tax rates that John mentioned and which you do not seem to
have addressed, and focussing only on what you have posted here, there are
still several problems:
A long integer numeric datatype cannot accept anything other than an
INTEGER, it CANNOT hold a decimal value. You must use either Single, Double
or Decimal to hold decimal values. Single will be adequate for your needs -
see Help for a description of the range of values, precision and storage
size of the various numeric fieldsizes.
If you have actually included the quotation marks in your default value and
validation rule, you will be forcing the data to text, which is not
acceptable. For the default value, Access will automatically convert this
back to a numeric value, but for the validation rule you will get an error
message that the expression is too complex. (Did you actually save your
table and try to enter data into it?)
A better method of limiting values to a particular set in a validation rule
is to use an expression such as:
In (0.06, 8.25)
The values you quote for your acceptable tax rates seem strange - depending
on how you do your calculation, they will give tax rates of either 6% and
825%, or 0.06% and 8.25%. If you want 0.06 to represent 6%, then 8.25% will
need to be entered as 0.0825. The Decimal Places setting in the table has
no effect on the data entered or stored - it is only used when a form/report
control is bound to the field (and it's generally better to set the required
display format in the form/report control itself, rather than in the
underlying table).
If you set the required property to no, you will need to modify your
expression(s) to deal with a null value if the user deletes the default
value.
Finally, you cannot get the tax component by dividing by the tax rate. You
must MULTIPLY by the tax rate. I'd also suggest that you get into the habit
of including brackets in your expressions to ensure that the evaluation
order is as you want it, rather than the default - it won't change things
here, but it's good practice. A suitable expression to calculate the total
would be:
Total: ([Taxable Subtotal]*[Tax (State/State)]) + [Taxable Subtotal]
or (giving the same result)
Total: [Taxable Subtotal]*(1 + [Tax (State/State)])
Here in Australia, invoices list the tax component as a separate amount - I
suspect they do in the US also. You will probably need a calculated value
for that:
TaxAmount: ([Taxable Subtotal]*[Tax (State/State)])
Then you could have yet another expression for the total:
Total: [Taxable Subtotal] + [TaxAmount]
HTH,
Rob
Elvis (AAA) said:
Tax (State/State) field should be able to select State or State that's why
I
made it text original. But as text I can't do the calculation. So I
remade
the Tax (State/State) to have Data Type: Number with the:
A) Field Size Long Integer
B) Decimal Places 2
C) Default value ="0.06",
D) Validation Rule ="0.06" Or ="8.25"
E) Validation Text: Must be, 0.06, Or 8.25.
F) Required No
G) Indexed No
I don't mind if the result of [Taxable Subtotal]/[Tax (State/State) is
displayed, I only need that so that I can add it on to [Taxable Subtotal]
which results in Total. Ideally I want to calculate using the Expression
Builder, example:
Total:[Taxable Subtotal]/[Tax (State/State)]+[Taxable Subtotal]
John Vinson said:
I created a table with 2 fields: Taxable Subtotal and Tax (State/State).
Taxable Subtotal: $0.00 <- Curency and Tax (State/State): 6% <-Text
I make query and I added both fields then Build:
Total: [Invoice Table]![Taxable Subtotal]/[Invoice Table]![Tax (NJ/NY)]
when I run the query I get #Error. Any Suggestions
Restructure your tables. They aren't going to be managable at all with
this structure.
A table with fields for State (text) and TaxRate (Double Float) would
be a start... but many states have multiple tax rates depending on the
county or even the municipality. This can be an extremely complex
design.
As noted elsethread, dividing the subtotal by the tax rate will not
get you a meaningful or usable number.
John W. Vinson[MVP]
