I've got a rounding function that looks like:

Function Round(varNumber As Variant) As Variant
    Round = Int((varNumber * 100 + 0.5)) / 100
End Function

I have a report which calls this function and passes in the number
"7090.515" as the result of a series of calculations.  When the number is
rounded, I get 7090.51.  If I do Round(7090.515) in the debug window, I get
7090.52.  Any idea why this same function yields different results on the
same number?  It appears to me to be related to the INT function, but I
can't figure out how to get it to behave as I desire.  I'd appreciate any
advice that you could give.

Thanks,

Jim

I don't know how you are getting 7090.51.  Every time I run your function I
get 7090.52.  However, you might want to do a search at dejanews.com (now
google.com) for rounding and VBA, there's been many functions posted just
for this activity, and they generally use larger powers of number to
effectuate an effective rounding function.
--
Steve Arbaugh
ATTAC Consulting Group
http://ourworld.compuserve.com/homepages/attac-cg

I think what is happening is that under some (unpredictable) circumstances,
7090.515 * 100 + 0.5 is returning 709051.9999.... instead of 709052.0.  This
comes about through the inaccuracies of floating-piont arithmetic.  I used
to use a rounding function similar to yours until I ran into a similar
problem where EXACTLY the same calculation on the same database record gave
a different result on two different computers.

Here is the function I use now:

Public Function RoundP(vNumber As Variant, iPlaces As Integer) As Variant
Dim vFactor As Variant
If IsNumeric(vNumber) Then
  vFactor = CDec(1 / 10 ^ iPlaces)
  RoundP = CDbl(Fix(vNumber / vFactor + Sgn(vNumber) / 2) * vFactor)
Else
  RoundP = vNumber
End If
End Function

This works because of the inclusion of a Decimal data type, so the whole
calculation is done in decimal, not floating-point.

I agree with SteveT that you should avoid naming a function the same as a
VBA or Access built-in function, hence my name "RoundP" for "Round to
Places".

I assume you, like me, dislike the way the built-in Round handles the final
5 - for example: Round( #.#x5, 2) will round the x UP if it is odd, and DOWN
if it is even.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.

Please post new questions to newsgroups.