INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...Congratulations for your great site. This site helped me more than university..."
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft: Access Forms FAQ
|
How to
|
How to work around the ROUNDing "problem" in Access ?
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
|
|
 |
|
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:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
