Handling Rounding Issues

Some of the more difficult to understand and debug errors in Access are rounding problems. These usually occur when adding up money values, but they can also happen in any math where a series of values is expected to add up correctly.

Rounding of Sums

One basic issue is not Access-related at all, but rather an issue whenever you add up a list of rounded numbers. For example, take a list of numbers that each represent one third of a dollar. If you add them up, you'll get 99 cents because the value of each portion (.33333333...) was truncated to .33.

A common place for this to show up is in a list of percentages that are supposed to sum to 100%. They often don't, because some precision was lost in the list. Then, you are faced with a decision—add up the actual numbers and show a total that's not 100, or just hard-code 100% so that it looks right. Most of the time you will want to use 100%, even though close observation will show that the numbers don't actually add up to 100. You may need to explain this kind of rounding error to your users.

Rounding Errors Caused by Floating Point Numbers

Another kind of rounding error comes from the way Access stores numbers in floating point fields. These fields cannot store certain numbers without losing some precision, so totals based on them may be slightly wrong. The best way to avoid these kind of rounding errors is to use the Currency data type for fields when they need to hold money values (as you might expect), or any numeric values that you want to use in calculations. The Currency data type is somewhat misnamed; it really can hold any decimal value.

Access uses the word Currency for both a data type and a format. This is unfortunate, because they really are two different things. The Currency data type is a method of storing the numeric values in the table. The Currency format only affects the display of numeric data. The two can be used independently or together.

Access Rounding Functions

Access has a built-in function (Round) to round numbers, but it may not work the way you expect. Most people think that any decimal ending in 5 should round up to the next higher number. However, Access uses a form of scientific rounding that works like this:

□ If the digit to be rounded is 0 through 4, round down to the lower number

□ If the digit to be rounded is 6 through 9, round up to the higher number

□ If the digit to be rounded is 5, round up if digit to the left is odd, and round down if the digit to the left is even.

This last rule is what surprises a lot of developers. Using this rule, Round gives the following results:

Yes, that's right. Both 1.5 and 2.5 round to 2 using the built-in Round function in Access VBA, because 1 is odd (round up) and 2 is even (round down). Here's another example:

Notice in this example that .54 5 rounds down, but .5 55 rounds up, for the same reason as described above. Since this can cause some trouble in business applications, developers have taken to writing their own rounding functions that behave the way business people expect. Here's an example of a function that rounds a trailing 5 upward to a specified number of decimal places:

Public Function RoundCurr(OriginalValue As Currency, Optional NumberOfDecimals As Integer) As Currency On Error GoTo Error_Handler

'returns a currency value rounded

to the specified number of decimals of

the Original Value

If IsMissing(NumberOfDecimals)

Then

NumberOfDecimals = 0

End If

RoundCurr = Int((OriginalValue

* (10 A NumberOfDecimals)) + 0.5) _

/ (10 A NumberOfDecimals)

Exit_Procedure:

Exit Function

Error_Handler:

MsgBox Err.Number & ", " & Err

.Description

Resume Exit_Procedure

End Function

This function can be placed in any module in your application and used whenever you want the business-style rounding that most users expect. Note that if you don't specify the number of decimals you would like, the function will assume that you want none and will return a whole number.

0 0

Responses

  • connie
    When have rounding number caused errors?
    7 years ago

Post a comment