by Peter Vogel
Two of this month’s articles deal with something that, I have to admit, I don’t give attention to: Precision. Working mostly in business applications,I use VBA’s Currency datatype frequently. Currency is really a misnamed datatype, as you don’t have to restrict its use to working with money. Currency is for any number that can get very large but requires, at most, four decimal points of precision. This allows you to round to three decimal places (something some of my business clients want) based on the number in the fourth decimal point.
However, both Chris Weber (When DateAdd Doesn't) and John Williams (Playing Tag with Validation) found themselves dealing with precision problems in their applications. The problem results from the inability of binary arithmetic to accurately represent the range of fractions available in the decimal system that we use. You can see how it would be difficult to represent the fraction 1/3 (which converts to the decimal numbering system as .3333...) in a system based on dividing by 2. If you’re working with the Currency datatype, all values are rounded to four decimal places and the chance for funny errors is reduced (but not eliminated). John and Chris were working with the Double datatype, which allows for almost infinite precision—and almost infinite problems.
Chris and John have different solutions to their problems because they had different goals: Chris needed to ensure that his numbers didn’t lose precision as he performed math on them; John needed to determine whether two numbers were equal after performing math on them.
Chris decided that he didn’t need decimals in his application: Working with whole numbers would give him the answer that he needed. So Chris solved his problem by only allowing integer inputs. If you do need at least some level of precision to the right of the decimal place, you can use a variation on Chris’ solution: Accept input with decimals but multiply all numbers by a factor of 10 to move any decimal values out into the whole number portion of the number. You can then drop the decimal portion (or round it into the whole number portion) of the result number. The result is an integer with which you can do integer arithmetic. After doing your integer math, you just have to remember to divide your result by whatever factor you originally multiplied by to get the result with the appropriate number of decimal places.
John took another path to solve his problem—fuzzy math. When you’re comparing decimal numbers, you’re probably willing to accept that 1.12234048592 is “pretty close” to 1.12234048593, even though they differ in the one-hundred billionth place (I think that I have that right, at least for North American audiences). For business applications the two numbers would be considered “the same.” However, as far as the computer is concerned, the two numbers are different and, if you compare them, they won’t be “the same.” So John establishes a range in which two numbers will be considered equal even if the computer thinks they’re different.
Programmers who began with Fortran, where, I believe, there are something like 16 different numeric datatypes, deal with these kinds of decisions all the time. Since most Access developers are business programmers, Access developers normally just sail past these problems until they turn around and bite us. I started with Cobol and then moved on to Fortran and PL/1 before moving to Visual Basic, thereby moving from simple numeric decisions to more complex ones and back to simple ones. I still remember my Fortran instructor telling me that “Cobol was for people who do not understand higher mathematical concepts—like adding and subtracting.” Working with VBA, it’s easy to forget that precision matters.