If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Best Data Type for up to 12-digit Accuracy (up to 9 after dec poin
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! |
#2
|
|||
|
|||
Best Data Type for up to 12-digit Accuracy (up to 9 after dec poin
On Thu, 2 Apr 2009 15:09:01 -0700, SH2008
wrote: 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). -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Best Data Type for up to 12-digit Accuracy (up to 9 after dec
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" wrote: On Thu, 2 Apr 2009 15:09:01 -0700, SH2008 wrote: 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). -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Best Data Type for up to 12-digit Accuracy (up to 9 after dec
On Thu, 2 Apr 2009 16:31:28 -0700, SH2008
wrote: 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! -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Best Data Type for up to 12-digit Accuracy (up to 9 after dec
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! -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "SH2008" wrote in message ... 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" wrote: On Thu, 2 Apr 2009 15:09:01 -0700, SH2008 wrote: 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). -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|