Two of this month’s articles deal with something that,I have to admit, I don’t give attention to: Precision. Working mostly inbusiness applications, I use VBA’s Currency datatype frequently. Currency isreally a misnamed datatype, as you don’t have to restrict its use to workingwith money. urrency is for any number that can get very large but requires, atmost, four decimal points of precision. This allows you to round to threedecimal places (something some of my business clients want) based on the numberin the fourth decimal point.
However, both Chris Weber (When dateadd Doesn’t) and John Williams (PlayingTag with Validation) found themselves dealingwith precision problems in their applications. The problem results from theinability of binary arithmetic to accurately represent the range of fractionsavailable in the decimal system that we use. You can see how it would bedifficult to represent the fraction 1/3 (which converts to the decimalnumbering system as .3333…) In a system based on dividing by 2. If you’reworking with the Currency datatype, all values are rounded to four decimalplaces and the chance for funny errors is reduced (but not eliminated). Johnand Chris were working with the Double datatype, which allows for almostinfinite precision—and almost infinite problems.
Chris and John have different solutions to theirproblems because they had different goals: Chris needed to ensure that hisnumbers didn’t lose precision as he performed math on them; John needed todetermine whether two numbers were equal after performing math on them.
Chris decided that he didn’t need decimals in hisapplication: Working with whole numbers would give him the answer that heneeded. So Chris solved his problem by only allowing integer inputs. If you doneed at least some level of precision to the right of the decimal place, youcan use a variation on Chris’ solution: Accept input with decimals but multiplyall numbers by a factor of 10 to move any decimal values out into the wholenumber portion of the number. You can then drop the decimal portion (or roundit into the whole number portion) of the result number. The result is aninteger with which you can do integer arithmetic. After doing your integermath, you just have to remember to divide your result by whatever factor youoriginally multiplied by to get the result with the appropriate number ofdecimal places.
John took another path to solve his problem—fuzzymath. When you’re comparing decimal numbers, you’re probably willing to acceptthat 1.12234048592 is “pretty close” to 1.12234048593, even though they differin the one-hundred billionth place (I think that I have that right, at leastfor North American audiences). For business applications the two numbers wouldbe considered “the same.” However, as far as the computer is concerned, the twonumbers are different and, if you compare them, they won’t be “the same.” SoJohn establishes a range in which two numbers will be considered equal even ifthe computer thinks they’re different.
Programmers who began with Fortran, where, I believe,there are something like 16 different numeric datatypes, deal with these kindsof decisions all the time. Since most Access developers are businessprogrammers, Access developers normally just sail past these problems untilthey turn around and bite us. I started with Cobol and then moved on to Fortranand PL/1 before moving to Visual Basic, thereby moving from simple numericdecisions to more complex ones and back to simple ones. I still remember myFortran instructor telling me that “Cobol was for people who do not understandhigher mathematical concepts—like adding and subtracting.” Working with VBA,it’s easy to forget that precision matters.