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

How to

How to work around the ROUNDing "problem" in Access ? by Scanman999
Posted: 12 Aug 03

Hi all, I've read through lots of threads here and there, and it looks like everybody has the same problem with roundings in Access 97/2000/2002. From what I've read somewhere around here, Access uses financial (brokers) roundings while Excel uses "what we've learned at school" roundings.
Where 0.5 becomes 1 for us, brokers round to the nearest integer... so 2.5 gives 2 and not 3.

Anyway, I came up with my own rounding routine and hope it will help you all. It actually does what we do in our head, looks at the digit following number of decimals we need and if it's greater or equal to 5, adds 1 to the preceeding digit and cut the whole number with the wanted decimal places.

Public Function RoundIT(Number, Places)
    RoundIT = Number
    If IsNumeric(Number) Then
        'Checks if it contains decimals
        If CLng(Number) <> Number Then
            xstr = CStr(Number)
            'Look if using Dot or Coma
            If InStrRev(xstr, ",") = 0 Then
                ystr = "."
            Else
                ystr = ","
            End If
            'Look for decimal position
            Y = InStrRev(xstr, ystr)
            'How many decimals in this number ?
            DecCount = Len(xstr) - Y
            'Continue only if number of decimals not already ok
            If DecCount > Places Then
                'Checks if someone asked to round with ZERO decimals
                If Places > 0 Then
                    If Mid(xstr, Y + Places + 1, 1) >= "5" Then
                        Mid(xstr, Y + Places, 1) = CStr(CInt(Mid(xstr, Y + Places, 1)) + 1)
                        RoundIT = CDbl(Left(xstr, Y + Places))
                    Else
                        RoundIT = CDbl(Left(xstr, Y + Places))
                    End If
                Else
                    'If number of decimal is ZERO, need to skip the decimal symbol too
                    If Mid(xstr, Y + 1, 1) >= "5" Then
                        Mid(xstr, Y - 1, 1) = CStr(CInt(Mid(xstr, Y - 1, 1)) + 1)
                        RoundIT = CDbl(Left(xstr, Y - 1))
                    Else
                        RoundIT = CDbl(Left(xstr, Y - 1))
                    End If
                End If
            Else
                RoundIT = Number
            End If
        End If
    Else
        Exit Function
    End If

End Function


Enjoy...

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms 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