I would appreciate an explanation for what I am seeing in my text boxes. I have developed a small db for bookkeeping purposes. It contains two fields [Debit] and [Credit] defined as follows: field size-double, format-standard, decimal places-2. On my data entry form the two fields are defined the same way. In the form footer I have three text boxes defined the same way with their sources being =Sum([Debit]); =Sum([Credit]); and =Sum([Debit]) - Sum([Credit]. The first two text boxes give me a running total of my debits and credits and the last tells me that my debits and credits are equal when I have finished posting my entries by displaying .00. Now to my question. When I put my cursor in the [Debit] text box in the form footer the number changes from 69,4567.34 to 694567.3399999 and the number in the [Credit] text box changes from 69,4567.34 to 694567.3399998. the difference field shows an even stranger number that ends E-08. As far as I know I have only entered two decimal place numbers in the debit and credit fields. How can it add numbers with only two decimal places and arrive at a 7 decimal number? I think this has caused a friend using my db to have a difference of .01 when actually her debits and credits are equal. Do I need to change my field definitions?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Number Definition 2
- Thread starter shade
- Start date
-
2
- #2
This is happening because you used Double as your data type. A Double value is represented internally as a binary floating point number. When you enter a value, represented in the decimal numbering system, it has to be converted to binary before it is stored.
The problem is that, when it comes to fractions, some perfectly ordinary (terminating) decimal fractions become repeating binary fractions. For instance, 2/10 in decimal is just .2, but in binary it's .001100110011... ("0011" is the repeating unit).
Since repeating fractions go on infinitely, but you only have room to store a finite number of binary digits, the result of the conversion is truncated and an approximate value is stored. This is inherent when using Single and Double values. The difference is very small, and when the number is converted back to decimal for display it's usually the same as the original value you entered, because the conversion back to decimal gets rounded to the number of decimal places you specified.
However, each stored value contained a tiny amount of error, and when you add up a lot of them, or even worse, multiply, the error accumulates. Eventually the total error accumulation may grow large enough to become apparent in the result (when converted to decimal with two decimal places).
Your difference field that ends in "E-08" illustrates how small the error is. This is scientific notation. E-08 means that the number preceding it should be multiplied by 10 to the -8th power--that is, one 100 millionth! If you change the Format property of the control displaying this to something other than General or Scientific, the difference will be too small to show and it will be displayed as 0.
When you're using Single or Double values, you have to be careful how you compare them in expressions. Because of the tiny amounts of error, values that appear equal when displayed might be slightly different, and will compare as unequal. To safely compare them for equality, you need to use the Round function before comparing them.
OK, so what can you do? Maybe the best thing is to change your Double fields to Currency. The Currency data type is specifically designed for accuracy in money calculations. Its only drawback is that it only maintains 4 decimal places, but it doesn't sound as if that would be a problem for this application.
Another possibility is to use the Decimal data type. Decimal values have 28 digits of precision, with any number of those 28 digits to the right of the decimal point. Decimal calculations are done in decimal, not binary, so accuracy is maintained. There are two drawbacks, though: (1) calculations done in decimal are inherently slower on a binary computer (which all desktop computers are), and (2) Decimal is sort of a red-haired stepchild in the current version of VBA, in that you can use Decimal values but you can't declare a variable with the Decimal data type. For variables you have to declare them as Variant and then use the CDec() function to assign Decimal values to them. This may make your VBA code "choppy and sloppy" and make it a little bit harder to maintain than does using the fully integrated data types.
Finally, you can continue to use Double data types, taking a slight risk that inaccuracies will pop up now and again. You can eliminate inaccuracies at display time by using the Format property of the control, choosing Fixed or Standard or Currency formats, all of which will round off the inaccuracy. You can eliminate calculation inaccuracies, once you find them (such as your friend's .01 difference) by using Round() judiciously, but it's practically impossible to come up with general rules about when and where to use it. And you have to bear in mind that your database will always contain only approximate values.
I hope this helps you understand the problem and the potential solutions. Rick Sprague
The problem is that, when it comes to fractions, some perfectly ordinary (terminating) decimal fractions become repeating binary fractions. For instance, 2/10 in decimal is just .2, but in binary it's .001100110011... ("0011" is the repeating unit).
Since repeating fractions go on infinitely, but you only have room to store a finite number of binary digits, the result of the conversion is truncated and an approximate value is stored. This is inherent when using Single and Double values. The difference is very small, and when the number is converted back to decimal for display it's usually the same as the original value you entered, because the conversion back to decimal gets rounded to the number of decimal places you specified.
However, each stored value contained a tiny amount of error, and when you add up a lot of them, or even worse, multiply, the error accumulates. Eventually the total error accumulation may grow large enough to become apparent in the result (when converted to decimal with two decimal places).
Your difference field that ends in "E-08" illustrates how small the error is. This is scientific notation. E-08 means that the number preceding it should be multiplied by 10 to the -8th power--that is, one 100 millionth! If you change the Format property of the control displaying this to something other than General or Scientific, the difference will be too small to show and it will be displayed as 0.
When you're using Single or Double values, you have to be careful how you compare them in expressions. Because of the tiny amounts of error, values that appear equal when displayed might be slightly different, and will compare as unequal. To safely compare them for equality, you need to use the Round function before comparing them.
OK, so what can you do? Maybe the best thing is to change your Double fields to Currency. The Currency data type is specifically designed for accuracy in money calculations. Its only drawback is that it only maintains 4 decimal places, but it doesn't sound as if that would be a problem for this application.
Another possibility is to use the Decimal data type. Decimal values have 28 digits of precision, with any number of those 28 digits to the right of the decimal point. Decimal calculations are done in decimal, not binary, so accuracy is maintained. There are two drawbacks, though: (1) calculations done in decimal are inherently slower on a binary computer (which all desktop computers are), and (2) Decimal is sort of a red-haired stepchild in the current version of VBA, in that you can use Decimal values but you can't declare a variable with the Decimal data type. For variables you have to declare them as Variant and then use the CDec() function to assign Decimal values to them. This may make your VBA code "choppy and sloppy" and make it a little bit harder to maintain than does using the fully integrated data types.
Finally, you can continue to use Double data types, taking a slight risk that inaccuracies will pop up now and again. You can eliminate inaccuracies at display time by using the Format property of the control, choosing Fixed or Standard or Currency formats, all of which will round off the inaccuracy. You can eliminate calculation inaccuracies, once you find them (such as your friend's .01 difference) by using Round() judiciously, but it's practically impossible to come up with general rules about when and where to use it. And you have to bear in mind that your database will always contain only approximate values.
I hope this helps you understand the problem and the potential solutions. Rick Sprague
- Status
Similar threads
- Locked
- Question