Calculated Fields in Query 1

[beckfla]

I have a problem with a calculated field on a query for a purchase order. Fields include Qty, Unit Price, Tax Rate.

I have created calculated fields in this query that work fine - Item Subtotal (calculates qty * unit price), and Item Total (adds item subtotal and tax amount). My problem is the calculated field for tax amount-

TaxAmt: CCur([ItemSubtotal]*[TaxRate])

The tax rate is listed as a decimal 0.065 or 0.06, depending on the tax rate where an item was purchased.

If I have a qty of 50 items that were $0.50 each, my subtotal (before tax) equals $25.00,however, the tax $ amount also comes out to $25.00 using the calculated field noted above. Unfortunately, I need to have tax totalled on each line item. It seems like this should work. Can anyone help on this? Thanks very much...GH

 

[PHV]

And this ?
TaxAmt: CCur([Qty]*[Unit Price]*[TaxRate])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckfla]

Thanks for your reply PHV- I tried

TaxAmt: CCur([Qty]*[Unit Price]*[TaxRate])

as you suggested - and it still shows up as $25.00 for the tax amount on a $25.00 purchase.

Any other ideas?

 

[PHV]

Just to be sure I'll add TaxRate in the field list ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckfla]

It seems like it's a problem with the taxrate field. Calculations work unless I am using the taxrate field. I tested the calculation by using just the qty field and the tax rate field and the tax amount came out as the same number as the qty field. TaxAmt: CCur([qty]*[taxrate])

My tax rate is listed as .06. ???

 

[PHV]

Try this to be sure:
RealTaxRate: 1.0*[TaxRate]
CurrTaxRate: CCur([TaxRate])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Wes1961]

You say:
Fields include Qty, Unit Price, Tax Rate

You have a space in "Unit Pricae" and "Tax Rate"

Then in your formula you are using "Unit Price" and "TaxRate" withno space.

TaxAmt: CCur([Qty]*[Unit Price]*[TaxRate])

However, if this was the case I would expect and error or the calculated field to be zero. Are you using SQL? If so, have you tried the Debug.Print TaxRate?

 

[beckfla]

My error: fields are Qty, UnitPrice, TaxRate.

Not using Sql.

Thanks...

 

[beckfla]

PHV,

If I use your suggestions

RealTaxRate: 1.0*[TaxRate]
CurrTaxRate: CCur([TaxRate])

I receive error - invalid syntax. Thanks...

 

[beckfla]

PHV,
I re-did your suggestion

RealTaxRate: 1.0*[TaxRate]
CurrTaxRate: CCur([TaxRate])

when I run the query, I get 1 instead of .06. I went back to my form, and this field is listed as a combo box that lists valid tax values (to prevent user errors). Perhaps this is the problem? Is there a way to change the expression so that it retrieves the correct tax rate? Thanks...

 

[PHV]

Have you a valid TaxRates table the combo refers to ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckfla]

PHV,

Yes, table is named TaxRate - it appears to be retrieving the taxid from the 1st column of that table instead of pulling from the 2nd column which is the actual tax rate. Is there a way to point to the 2nd column? Thanks again...

 

[PHV]

You have to join the TaxRate table in the underlaying query.
Or, if performance isn't an issue:
TaxAmt: CCur([ItemSubtotal]*DLookUp("[TaxRate value name]","TaxRate","[TaxRate key name]=" & [TaxRate]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckfla]

PHV,
I sure appreciate your help. I joined the taxrate table in the query and the field for taxrate now shows the correct taxrate, however, the calculation is still using the primary key field when computing taxamt. I'm getting frustrated and imagine you may be also...

 

[PHV]

No ambiguity on the field names ?
Get rid of the combo field in the select list keeping only the TaxRate.Value field

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckfla]

PHV - You have been very helpful - and patient. It is working great now. Thank you so very much!!!