Hello Everybody,

I have been trying to deal with the rounding issue in access. I thought I had a solution with some code from here (dont remember who posted it, but THANKS.)Which is:

Public Function Round(ByVal NumberToRound As Variant, Optional NumberPlaces As Long = 0, Optional FirstValueToRoundUp As Byte = 5) As Variant

'Custom rounding function to avoid the banker's rounding Access uses in the Round() function

On Error GoTo Err_Round

Dim dblFactor As Double
Dim intSign As Integer
Dim dblModifier As Double

If Not IsNumeric(NumberToRound) Then
Err.Raise alERR_ARG_NOT_NUMERIC, CodeProject.Name, "The argument must be numeric"
End If

dblFactor = 10 ^ NumberPlaces
intSign = IIf(NumberToRound < 0, -1, 1)
dblModifier = CDec((10 - FirstValueToRoundUp) / 10)

NumberToRound = Abs(CDec(NumberToRound))
Round = CDec(Int((NumberToRound * dblFactor) + dblModifier) / dblFactor)
Round = Round * intSign

Exit_Round:
Exit Function

Err_Round:
alRound = CVErr(Err.Number)
Resume Exit_Round

End Function

Anyway, it works fine except I am having trouble with the number 46.125 which is should round to 46.13, but it is going to 46.12!!!!!!
Please Help!!!

How are you calling it? I just tested it briefly and it returned the expected value. As an aside, I would not name the function "Round", to avoid confusion with the built-in function.

Double and Single precision (imprecision, really) floating point numbers are notoriously unfit for delivering expected/accurate results from mathematical operations.

I wouldn't put much stock in this rounding function.

Hey,

Do you have a better suggestion?

Thanks.

Callingin both in a query and a bound textbox by =round([textbox],2)

Thanks

Well, as just a rough shot, I'd probably convert the textbox value to an explicit string, truncate just to the right of the significant digit (which means you have to find the decimal point), then interrogate that rightmost digit as CInt(Right(myTruncatedNumber,1)) to see if it's >= 5, then adjust(round) the significant digit accordingly... then finally convert it to Currency data type.

You would also have to account for numbers having fewer than the number of significant digits (i.e., ones which don't need to be rounded)

But if you hunt around, I'm sure there are a bunch of other strategies out there you could use.

HTH,
John

I came up with this function, see if it helps you. (If it doesn't work your feedback will drive me to research some more to develop a better solution):