This Round Thing

I work on Ms-Access, VBA and

I have a legacy code and inside it there is a self-written ROUND function, as follows:

Public Function Round(v1 As Variant, v2 As Byte) As Double

If Not IsNumeric(v1) Then

Round = 0

Else

Round = INT(v1 * 10 ^ v2 + 0.5) / 10 ^ v2

End If

End Function

I never knew why it was there but as the old saying goes: if it's not broken, don't fix it!

Today I found out that it is broken, i.e. it simply doesn't round to the required precision.

Thanks to Allen Browne, and more specifically to the MVPs of Access, I found out that

a. Access rounding may not existed when the database I am working on was created

b. Even if it did, it uses "banker's rounding" which may not be adequate

c. I shouldn't be using cast as INT, but instead, a different type of CAST.

That's because INT truncates the decimals, which is not my intention.

Like this:

Public Function Round(v1 As Variant, v2 As Byte) As Double

If Not IsNumeric(v1) Then

Round = 0

Else

Round = CLng(v1 * 10 ^ v2 + 0.5) / 10 ^ v2

End If

End Function

And that makes the rounding beautifully done!

The beauty of the function supplied by the MVP's of access is that it can deal with very large numbers, which is a bit problematic in my version.