Navigation:  Articles > Sep-1997 >

Tip: Getting the Math Right

Previous pageReturn to chapter overviewNext page

Dana Ferguson          
 
Luke Chung's article,"When Access Math Doesn't Add Up," in the February 1997 issue, uses FMS' Total Access Sourcebook Round_TSB function as the way to properly round numbers. He's nearly right, but if you pass Round_TSB 1.005 and ask for it to be rounded to two places, you'll get 1 back. On the other hand, 0.005 gives .01, 2.005 gives 2.01, and so on, and Luke is back walking on water again. He stays there for most other numbers I could think of.
 
You can expect more problems if you're converting Access 2.0 applications to Access 97, and the company likes to get the same answer as it did last year. The bogeyman appears at the same 1.005 mile marker: in Access 2, Format(1.005,"0.##") will return 1.01, but the same command in Access 97 returns 1.
 
It turns out that Int has trouble with the floating point math for Double variables. When Int does the calculation, it sees 1.005*10^2 + .5 as something just shy of 101. Put another way, Int(101) returns 101 as it should, but Int(1.005*10^2 + .5) returns 1. The fix, at least for Access 97, is to use the new CDec() function, which allows you to convert the Double datatype to the more disciplined Decimal datatype. Here's a round function that seems to get it all right:
 

Function Rnd_Num(dblNum As Double, _

 intDecimals As Byte) As Double

On Error GoTo errRndNum

   Dim dblFactor As Double, dblTmp As Double

   dblFactor = 10 ^ intDecimals

   dblTmp = CDec(dblNum * dblFactor + 0.5)

   Rnd_Num = Int(dblTmp) / dblFactor

Exit Function

errRndNum:

   MsgBox Err.Description, vbCritical, _

     "Take Up Knitting Instead.."

   Exit Function

End Function