INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Functions

The Round() function rounds 2.45 to 2.4 and is correct. Why? by davemcdonaldireland
Posted: 17 Jun 03 (Edited 24 Sep 03)

Before you use the Round() function in Access, please read this FAQ or you may introduce calculation errors into your application:

Syntax: Round(expression[, numdecimalplaces])

The Access Round() function appears to be a handy way of rounding decimal values to a given number of decimal places. For example, Round(2.341, 2) returns 2.34.

However, it does not perform rounding the way you might expect. Take this example:

Round(2.385,2)

You would expect this to return 2.39, but  if you open an immediate window and type ?Round(2.385,2) you will get the following output:

2.38 (not 2.39)

Even though the last digit was 5, it rounded DOWN.


If this is not the behaviour you expected, then hereÆs the explanation:

The Round() function does a round-to-even, not the more well-known round-to-larger. If the argument ends in 5, the number may be rounded up or down to achieve an even rightmost figure.

So in our example above:

2.385 could be rounded to either 2.38 or 2.39. The former has an even rightmost figure, so this is the result.

The reasoning behind this is to eliminate cumulative errors over a series of calculations. Put another way, 100 half pennies should round to be equal 50 cents, not 100 cents.


A RoundToLarger() function:

Public Function RoundToLarger(dblInput As Double, intDecimals As Integer) As Double
    
    'Implements a variant of the Round() function, that rounds-to-larger
    'rather than rounds-to-even, like in Excel:

    Dim strFormatString As String 'Format string
    Dim strResult As String

    'If input is zero, just return zero. Else format as appropriate:
    If dblInput <> 0 Then
        strFormatString = "#." & String(intDecimals, "#")
        If Right(strFormatString, 1) = "." Then
            strResult = Format(dblInput, "#")
        Else
            strResult = Format(dblInput, strFormatString)
        End If

    Else
        strResult = "0"
    End If
    
    'If the result is zero, Format() will return "." - change this to "0":
    If strResult = "." Then
        strResult = "0"
    End If
    
    RoundToLarger = CDbl(strResult)
    
End Function


Acknowledgement: Thanks to ôhnawebmasterö for help in compiling this FAQ.


Dave Mc Donald
www.mcdonaldit.com


Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close