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