Bankers Rounding

[MTSPEER]

Hello,



I've been reading why Access doesn't always round up properly and it's because of Bankers Rounding. Does anyone know how to fix this? Is there any way to do it in a query? Im using an update query. I have a field called ProjectedAverage. Need it in the format of 3 decimal places. How I had the query before was Round([ProjectedAverage],3). The table name is GAF btw. Thanks for your help!

[orange]

For info and alternative code/functions see Rounding

Access does Bankers Rounding. If you want something different, you have to code it.
Good luck

[ItsMe]

If I recall correctly, In Access, the Round() function rounds different data types differently. So, if you round to the whole number, an Integer is returned. It is the Integer that is using Banker's Rounding. I could be mistaken, though.
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

[orange]

Here is more info on MSAccess rounding from Allen Browne.

[MTSPEER]

Okay I put the code into a module. and the function is called Round03. I can put it in a select query like Round03([ProjectedAverageRiskScore]) right? But when I run it it returns errors

[orange]

If you want focused responses, then

1. tell us exactly the issue/opportunity in simple English
2. show us the code you are using
3. show us the result --error message/bad number etc


"It returns errors" is not helpful and just leads to additional dialog to get the details.

Why did you pick 3 out of the 17 or so available? Did you test it with some sample data before using it in your "real data"?

[ItsMe]

Here is more info on MSAccess rounding from Allen Browne.

Okay, that's the Int rule I was thinking about. Where Integer will truncate any fraction to whole number by simply dropping the fraction.

[MTSPEER]

I thought the Round03 Function was for rounding 3 decimal places..I have the table name GAF..Field Name ProjectedAverage. I want it to round to 3 decimal places. And I thought you could use a public function in a query. So I used Round03([ProjectedAverageRiskScore]) in a select query as an expression. And it returned #Error

[orange]

Please show the code you used.

Here is round03 from the site I suggested.

Public Function Round03(dblNumber As Double, Optional numDecimalPlaces As Integer) As Double
' by Ken Getz and Mike Gilbert, VBA Developer's Handbook (Sybex, 1999), page 180
' [slightly altered to match our specifications]
' NOTE: overflows if Abs(numDecimalPlaces) is too large
  Dim dblFactor As Double
  dblFactor = 10 ^ numDecimalPlaces
  ' CDbl is necessary else eg. Round03(1.2345, 3) would return 1.234
  Round03 = Int(CDbl(dblNumber * dblFactor + 0.5)) / dblFactor
End Function

[MTSPEER]

Yes I put that into a module. Sorry Ive never used a module to update a field in a table before. I don't know what is the process after that

[June7]

Round() in Access uses banker's rounding (aka even/odd) but not in Excel. Format() and FormatNumber function do not use even/odd, however the result is a string and that can cause issues if used in subsequent calcs so have to convert back to a number (CInt, CDbl).

My version of custom round function:

Function RRound(FieldValue As Variant, Optional intPos As Integer = 0) As Variant
'--------------------------------------------------
' Function RRound() rounds value to designated decimal position.
' If argument does not contain data, RRound returns null.
' Use because intrinsic Round uses even/odd (banker's) rounding.
' Also, Format and FormatNumber functions don't use even/odd but
' generate a string result which is often inconvenient.
'--------------------------------------------------
Dim strZeros As String
Dim i As Integer
If intPos = 0 Then
    strZeros = 0
Else
    For i = 1 To intPos
        strZeros = strZeros & 0
    Next
    strZeros = "0." & strZeros
End If
RRound = IIf(Not IsNull(FieldValue), Val(Format(FieldValue, strZeros)), Null)
End Function

More:
http://www.allenbrowne.com/round.html

And for row-level stats (because of non-normalized data structure):
https://support.microsoft.com/en-us/kb/kbview/189584

[MTSPEER]

that only rounds to the whole number right? I need 3 decimal places

[June7]

Both functions round to any number of decimal places.

Function code goes in a general module. Call the function from query, textbox, macro, VBA.

In a textbox:

=RRound([fieldname], 3)

[MTSPEER]

And I used that code and the null records report #Error in the field. and the records that has values rounded them to a whole number

[MTSPEER]

Omg I'm an idiot! thank you very much