Oct 1 2009, 07:55 PM
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"
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
alRound = CVErr(Err.Number)
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!!!!!!
Oct 1 2009, 08:12 PM
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.
Oct 1 2009, 09:00 PM
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.
Oct 2 2009, 09:58 AM
Do you have a better suggestion?
Oct 2 2009, 01:04 PM
Callingin both in a query and a bound textbox by =round([textbox],2)
Oct 2 2009, 06:03 PM
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.
Nov 2 2009, 01:40 PM
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):
Public Function fRoundNum(varNum As Variant, Optional intPlaces As Integer = 0) As Double
'Replacement for MS VBA Round(), which uses Bankers Rounding rule
'fRoundNum rounds .5 Up
fRoundNum = Int(Abs(varNum) * (10 ^ intPlaces) + 0.5) / (10 ^ intPlaces)
If varNum < 0 Then fRoundNum = -fRoundNum
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here