Access not calculating correctly 2

[wendyp]

Hello - another problem with decimals..

In the tax table, I have the following pertinent fields:

numStateTaxRate
numCountyTaxRate
numCityTaxRate
numDiscountRate

They all have a data type of currency, format of percent and I've tried with decimal places being "auto" and set to 6.

I have a bound form, showing these fields, formatted as a percent with 4 decimal places:

State Rate: 0.0000%
County Rate: 6.3000%
City Rate: 1.8000%
Discount Rate: 35.0000%

Then I have an unbound field that calculates the total:

Total Tax Rate:
=([numCountyTaxRate]+[numStateTaxRate]+[numCityTaxRate])

The above is accurate.

Then I have another unbound field to calculate the rate after discount:
Effective Rate: =CCur([numCountyTaxRate]+[numStateTaxRate]+[numCityTaxRate])*CCur(1-[numDiscountRate])

I have tried both with and without Ccur.

So the answer should be
(0 + .063 + .018) * (1 - .35)
.081 * .65 = 0.05265

But on my form, it shows up as 5.2600%

Why isn't it keeping the last 5? It isn't rounding either.

I thought the currency data type (even if formatted as a percent) would be accurate to 15 decimal places.

What am I missing?

Thanks,
/Wendy

 

[dhookom]

Change the currency data type to something else like decimal. Currency will store to only 4 decimal places.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[wendyp]

Thanks!

I had been told to use currency, but I went and checked after your post and it is accurate to 15 decimal places, but on the left side of the decimal! Not what I need for percentage calculations.

I've converted all my percentages to decimal with a scale of 15 (numbers on the right of the decimal) and I should have all the accuracy I need.

Now I'm getting mismatched data types in my code - it looks like I must change currency to variant - there doesn't seem to be a decimal type in VBA (well, it says that decimal is a sub-type of variant). Will that cause problems?

Thanks again,
/Wendy

 

[PHV]

Now I'm getting mismatched data types in my code
Use the CDec function.

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

 

[wendyp]

That worked like a charm. Thanks a million! A star for you.

/Wendy