Rounding to one decimal place - Access 97 1

[wbrannon]

Using Access 97, I'm doing a calculation in which I want the result to be rounded to one decimal place so that I may use it in another calculation. Does anyone have any idea how to accomplish this?

Wbrannon@hotmail.com

 

[RickSpr]

Are you sure you want to round it before using it in further calculations? Rounding introduces error, and as a general rule it's best to postpone rounding to the last, so that the error doesn't multiply. That's why Access' built-in support for rounding occurs when converting the number to a display format, such as with the Format$ function.

But if you really want to do it, here's a function that will round to an arbitrary number of positions. This particular function has a risk of overflow, so if your values may be very large you should use an alternative.

    Public Function Round(Value As Double, _
                          Optional NumDecimals As Integer = 0) _
                          As Double
    ' Rounds Value to NumDecimals places. For example, if
    ' NumDecimals = 1, 1234.567 would be rounded to 1234.6.
    ' Rounding is to lower value when next digit is 0 to 4,
    ' to higher value when next digit is 5 to 9.
    ' If NumDecimals is negative, an 'Invalid Procedure Call
    ' or Argument' error occurs.
        Dim factor As Long, temp As Long
        
        If NumDecimals < 0 Then Err.Raise 5
        factor = 10 ^ NumDecimals
        temp = Fix(Value * factor + 0.5)
        ' For &quot;banker's rounding&quot; change the above to:
        ' temp = CLng(Value * factor)
        Round = temp / factor
    End Function

Rick Sprague