How can I calculate tax for 2 states?

[Guest]

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

 

[Rob Parker]

Seems like your tax field is Text datatype. If so, you can't possibly do
any calculations with it.

Rob

BTW: Dividing a taxable subtotal by the tax rate is not going to give you a
total - in the sense that almost everybody would be using it. It won't even
give you the tax amount.

 

[John Vinson]

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]

 

[Guest]

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]

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]

 

[Rob Parker]

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

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]

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]

 

[Jamie Collins]

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.

I don't think that is correct. I think the validation rule would allow
a null value to pass. For example, the UPDATE statement below causes no
error:

Sub test4()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test4 ( key_col INTEGER NOT" & _
" NULL PRIMARY KEY, data_col DECIMAL(3, 2)" & _
" DEFAULT 0.06, CONSTRAINT data_col__values" & _
" CHECK (data_col IN (0.06, 8.25)));"

' Apply default value
.Execute _
"INSERT INTO Test4 (key_col) VALUES (1);"

Dim rs
Set rs = .Execute( _
"SELECT key_col, data_col FROM Test4;")
MsgBox rs.GetString(2, , , , "(null)")
rs.Close

' NULL the default value
.Execute _
"UPDATE Test4 SET data_col = NULL" & _
" WHERE key_col = 1;"

Set rs = .Execute( _
"SELECT key_col, data_col FROM Test4;")
MsgBox rs.GetString(2, , , , "(null)")
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Rob Parker]

Hi Jamie,

I thought that's what I was saying - if it's possible to have a null value
because the field is not required, then the OP will need to use expressions
that will deal with a null (most easily by using the nz() function). Your
example seems to demonstrate that a null value is indeed possible.

As an aside, if i look at the table Test4 created by your code in design
mode, there is nothing in the validation rule for the data_col field. I
would have expected it to contain "IN (0.06, 8.25)" (without the quotes).
The CONSTRAINT exists - if I try to enter other data I get the "...
prohibited by validation rule ..." error. Is it normal that table design
parameters for a table created via code do not show in the standard table
design user interface?

Rob

 

[Guest]

Field Size: Single
Validation Rule: In (0.06, 8.25)
Build Expression: Total: ([Taxable Subtotal]*[Tax (State/State)]) + [Taxable
Subtotal] works great.

Thank You, for all of your help. I have been trying to get that result for
like 2 weeks and now I have it thanks to you.

I want to have the Tax (State/State) field in the table to be selected but
the only way I know of is by the Data Type to be set to Text.
Can I use the Data Type Yes/No in the same ways as Text so that I can select
or define Yes to be 0.06 and No to be 0.0825? Is that possible, I never
define Data Type Yes/No any suggestion welcomed.

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

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]

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]

 

[Jamie Collins]

I thought that's what I was saying - if it's possible to have a null value
because the field is not required, then the OP will need to use expressions
that will deal with a null (most easily by using the nz() function). Your
example seems to demonstrate that a null value is indeed possible.

What I am saying is that the OP does not need to use an expression that
will deal with a null. Look again at the validation rule (i.e. CHECK
constraint) in my SQL and note I have not used a NZ() type construct,
yet the null value does not fail the rule and the null value is allowed
to pass.

The SQL engine will handle the NULL something like this: "I have this
'unknown' value, being a placeholder value for an 'actual' value that
is expected some time in the future. Can I say for sure at this point
in time that this future expected value will fail the rule? No I can't,
therefore I will allow the NULL placeholder value to pass, the
INSERT/UPDATE should succeed and I'll test the 'actual' value when it
is known."

if i look at the table Test4 created by your code in design
mode, there is nothing in the validation rule for the data_col field. I
would have expected it to contain "IN (0.06, 8.25)" (without the quotes).
The CONSTRAINT exists - if I try to enter other data I get the "...
prohibited by validation rule ..." error. Is it normal that table design
parameters for a table created via code do not show in the standard table
design user interface?

There is no way of setting the 'validation text' property, a Jet
proprietary syntax, using SQL DDL code. This isn't due to a limitation;
rather, it's because Jet's SQL DDL is based on the ANSI SQL-92 standard
SQL DDL which has no 'validation text'. I suppose I could have used
ADOX to set the property but I would've made the code seem more
complicated ;-)

Jamie.

--

 

[Rob Parker]

See comments in-line:

Validation Rule: In (0.06, 8.25)

I think you mean: In (0.06, 0.0825)

I want to have the Tax (State/State) field in the table to be selected but
the only way I know of is by the Data Type to be set to Text.

I'm having trouble understanding what you're talking about. You do not
"select a field in a table", at least in the sense that I think you're
trying to use it. What you should be doing is joining a table containing
tax rates to your main table, containing the data you are working with, in
the query which is producing the calculated fields. In a post several days
ago, you were advised to restructure your tables; until you do so, you will
be in a mess. If what I am saying means nothing to you, then go and buy a
beginners book on Access, and read, learn and inwardly digest.

Can I use the Data Type Yes/No in the same ways as Text so that I can
select
or define Yes to be 0.06 and No to be 0.0825? Is that possible, I never
define Data Type Yes/No any suggestion welcomed.

No, you cannot do that! A Yes/No datatype is used to hold boolean data -
that means data that can be only Yes or No, also referred to as True or
False, and represented internally in Access tables by the values -1 (for
Yes, or True) and 0 (for No, or False).

Rob

<snip>

 

[Rob Parker]

See comments in-line:

What I am saying is that the OP does not need to use an expression that
will deal with a null. Look again at the validation rule (i.e. CHECK
constraint) in my SQL and note I have not used a NZ() type construct,
yet the null value does not fail the rule and the null value is allowed
to pass.

I wasn't suggesting that the OP needed an expression that will deal with a
Null when he is entering data into the table, I was saying that he will need
to cope with nulls in data retrieved from the table when he is using it in
an expression. And I still believe that it correct - if the field is not a
required field, then it can contain null.

There is no way of setting the 'validation text' property, a Jet
proprietary syntax, using SQL DDL code. This isn't due to a limitation;
rather, it's because Jet's SQL DDL is based on the ANSI SQL-92 standard
SQL DDL which has no 'validation text'. I suppose I could have used
ADOX to set the property but I would've made the code seem more
complicated ;-)

That's an interesting thing to know. It means that it's possible to come
across a database (such as your example) containing tables with fields which
have constraints, but there seems to be no way, in Access itself, to see
what the constraints are. Or at least, no simple way: the in-built
documenter does not show it, and both the DAO and ADO COMs do not appear to
have any property which exposes the constraint. In that situation, how does
one view the constraint?

Rob

 

[Jamie Collins]

I wasn't suggesting that the OP needed an expression that will deal with a
Null when he is entering data into the table, I was saying that he will need
to cope with nulls in data retrieved from the table when he is using it in
an expression.

We seem to be talking across each other, then. Apologies if I've caused
confusion. Yes, NULLs behave differently in SQL DML: in SQL DDL they do
not cause a row to fail to be inserted/updated, whereas in SQL DML a
null value will cause a row to be removed from a resultset (unless
explicitly specified, in both cases, of course).

it's possible to come
across a database (such as your example) containing tables with fields which
have constraints, but there seems to be no way, in Access itself, to see
what the constraints are. Or at least, no simple way: the in-built
documenter does not show it, and both the DAO and ADO COMs do not appear to
have any property which exposes the constraint. In that situation, how does
one view the constraint?

You are correct that the Access UI functionality is still playing
'catch up' with the Jet engine (itself deprecated half a decade ago
<g>).

ADO does provide means of retrieving the CHECK definitions, including
Access validation rules. You can use the ADO method OpenSchema to get
recordsets of the relevant information.

Use adSchemaTableConstraints with the table name then filter the
resulting recordset for CONSTRAINT_TYPE = 'CHECK'; however, this only
gets you the CHECK name and definition. Use adSchemaCheckConstraints
and the resulting recordset for CONSTRAINT_NAME to get the table
against which the CHECK was defined (and column name if that matters to
you, however for Jet the seems to be no way of definig a column-level
CHECK). Remember that Jet, unlike other engines (including SQL Server),
does allow multiple tables to be referenced in a CHECK constraints, but
only changes to the table against which it was defined will cause the
CHECK to be invoked.

There are a few of gotchas: for Access-created Validation Rules it is
not a simple 1:1 mapping between CONSTRAINT_NAME values in each
recordset: adSchemaTableConstraints [<table>].[<column>].ValidationRule
maps to adSchemaCheckConstraints [<column>].ValidationRule; also,
adSchemaTableConstraints is sometimes terminated with a Chr$(0),
sometimes not.

The most frustrating limitation is that the adSchemaCheckConstraints
CHECK_CLAUSE column is defined as NVARCHAR(255), yet another reason for
splitting a monster validation rule into more granular (hence more
useful) smaller rules.

Jamie.

--