|
By Albert D. Kallal Simple tips to avoid math rounding errors in your applications.
Access is a great business tool and while we don't as a rule have to worry or think about rounding errors, there ARE some simple tips to keep one out of trouble. I dare say that talking about rounding numbers is LESS exciting than watching paint dry. So this is not going to be a technical article but simply some tips to keep you folks out of trouble. However, just like some opening scene in a movie to GRAB your attention? Here is a STRONG cup of wake up Access coffee Take this simple code in Access (or even Excel) and run it!
You can see that after just 7 additions rounding is occurring! Note how after JUST 7 simple little additions Access is now spitting out wrong numbers and has rounding errors! More amazing? The above code runs the SAME in Excel! Ok, I am sure I have your attention now! In fact PLEASE keep reading since if you have to run out for coffee or off to work then after seeing above your confidence in Access likely has just been shaken to the ground! So such simple math fails? You mean Access cannot add up 7 simple little numbers without having errors? How can I even do payroll then? The basic concept and ALL you need to know here is that computers store real (floating) numbers only as approximate. And integer values are stored exact. I am not going to create a long article on explain why the above happens, but I do want experienced or even budding users of computers and especially those writing code in Access or Excel to be AWARE of the above issue and more important HOW to avoid the above simple error issue. The KEY concept here is that computers store and represent integer values EXACT and adding such numbers will NOT cause rounding errors. The result of this new found knowledge and HOW to stay out of trouble? Simply use integer data types when working with financial data in Access. In fact Access as a built in "handy dandy" data type called "currency". This data type is what we call a scaled integer. (so it is a integer value that ALSO stores the number of decimal places). These numbers are thus not floating point numbers but are stored as exact values along with a decimal place holder. The result is we are adding and working with integer values. In the case of Access the currency type is a integer with a scale of 4 decimal places. For any database or even just tables of data in Access requiring financial data then simply choose the currency column type. In the table layout mode you find the currency choice here:
And if you in table design mode, then this choice is the magic that will keep you out of trouble:
Fixing our sample VBA code. Again, the simple solution is to use currency data type.
My experience in writing business software says you rare need anything more then 4 decimal places. However some types of interest calculations may need additional precision. There are two simple solutions. One is to "scale" the data manually in your code. So before you start your calculations, you move the decimal point over say 2 places. So if you require 2 extra decimal points "more" then currency such as 6 places then multiply your one of your numbers by 100. Do the calculations, and then divide my 100 when done (this will cut/round your result back to 4 digits). so $123.45 now becomes $12345.00 While the above trick of scaling numbers was often used in the past when writing business software, today such requirements occur far more rare. However in this day and age manually having to "scale" numbers is not your cup of tea then access has another choice of "decimal" number. Really big numbers! Access has another choice for really large numbers called decimal. This choice allows a WHOPPING 28 digit long number. And you can choose the number of decimal places out of that huge number. In fact the setting and choice is "scale" and once again this built in feature means YOU do not have to write code to scale the integer number. So this number saves the decimal point and scales the number for you. Again this choice does NOT have rounding errors. So if you need to work with really large numbers, say 18 digits with 10 decimal places to the right for a huge total of 28 digits? Use + choose the decimal type in the table designer:
Note how you have to choose number type (first hand) and then choose the decimal type (second hand in above). Note how the above is setup. Precision - this is your TOTAL number of digits for the numberScale - this is WHERE you want the decimal point to be moved to.Decimal places - CAUTION this is only the format for display but likely and often the value here should be the same as your scale.At the end of the day this means that Access has a BUILT
IN scaling system for working with REALLY large numbers and numbers that
are NOT subject to rounding errors (they are scaled integers). In VBA you do NOT have a built in variable type called decimal type - so use the variant data type. At the end of the day most Access developers don't have to worry about math and precision if you simply choose "currency" for your financial data columns. And if you need some really large numbers? You can tackle such problems with the built in scaled "decimal" number system available in Access. The decimal scale system in Access is rather nice since it eliminates the need to manually scale your numbers in application - Access does all the scaling automatically for you.
best Regards,
Albert D. Kallal
|
