Best Data Type for up to 12-digit Accuracy (up to 9 after dec poin

[SH2008]

Hi there--

I need to store numeric data that should be able to handle up to 12 digits
with up to 9 digits behind the decimal point. Thus, Currency is out and
Double seems the only data type in Access capable of handling this many
digits. I was informed, however, that Access (sometimes?) creates "ghost
digits" for double numbers -- extra random digits after the last digit.

Assuming that I enter a bunch of percentage numbers with up to 9 decimal
digits totaling 100%, my application CAN NOT EVER have rounding errors that
would cause the sum of the numbers to vary from 100.000000000% exactly.

What scenario would you guys recommend for this requirement?

Thanks!

 

[John W. Vinson]

Hi there--

I need to store numeric data that should be able to handle up to 12 digits
with up to 9 digits behind the decimal point. Thus, Currency is out and
Double seems the only data type in Access capable of handling this many
digits. I was informed, however, that Access (sometimes?) creates "ghost
digits" for double numbers -- extra random digits after the last digit.

Assuming that I enter a bunch of percentage numbers with up to 9 decimal
digits totaling 100%, my application CAN NOT EVER have rounding errors that
would cause the sum of the numbers to vary from 100.000000000% exactly.

What scenario would you guys recommend for this requirement?

Thanks!

There are (or were, they may have been fixed in a service pack) some quirks
about sorting negative numbers in the Decimal datatype - but I'd give it a
try. Set the Precision to 12 and the Scale to 9 (precision can be up to 18 I
believe).

 

[SH2008]

Thank you, John.

I thought 'decimal' is not native to Access and gets converted to double for
calculations via VBA. Would I not end up with the same problem?

 

[John W. Vinson]

Thank you, John.

I thought 'decimal' is not native to Access and gets converted to double for
calculations via VBA. Would I not end up with the same problem?

I don't know. Crystal has raised this same issue off the newsgroups, I'll post
back when I learn more!

 

[Allen Browne]

The Decimal is a scalar data type, where you can set the number of decimal
places you want at the table level.

In VBA, you can create a Variant, and typecast a value with CDec(). I expect
that the result of operating on two variants of subtype Decimal would be a
Decimal, but that's without testing. Perhaps you would like to test it with
different operators and see what you find. This kind of thing:
Dim var1 As Variant
Dim var2 As Variant
var1 = CDec(99.123456789)
var2 = CDec(88.123456789)
Debug.Print VarType(var1 + var2)

Of course you can't set the scalar level in VBA. Again, I really don't know
if this will do any good, but you could try giving it some, e.g.:
var1 = CDec(2.000000001) - CDec(0.000000001)

JET does have some issues handling with decimals, e.g.
http://allenbrowne.com/bug-08.html

In other words, run some tests, Crystal, and have fun!