Stop Banker's Rounding in Access

[Tajaldeen]

I'm using this code to stop banker's rounding but it's not perfect so far

Round(Int((([FieldName]+0.499)*2)/2)

Does anyone has more efficient way to make normal rounding such the one on Excel.

 

[ebs17]

Function fctRound(varNr As Variant, Optional varPl As Integer = 2) As Double
'by Konrad Marfurt + ("" by) Luke Chung + Karl Donaubauer

   'raus hier bei nicht-nummerischem Argument
   If Not IsNumeric(varNr) Then Exit Function

   fctRound = Fix("" & varNr * (10 ^ varPl) + Sgn(varNr) * 0.5) / (10 ^ varPl)

End Function

 

[arnelgp]

i modified the Rounding function of Markk here:
Round to the nearest quarter (15 minutes) | Access World Forums (access-programmers.co.uk)

Function fnRoundToNearest(d1 As Double, RoundTo As Double) As Double
    fnRoundToNearest = CLng(d1 / CDbl(RoundTo)) * RoundTo
End Function

usage:

fnRoundToNearest([FieldName], 0.5)

 

[MajP]

The OP is unclear on how they wanted Bankers rounding stopped because that can be interpreted differently. My assumption is how Xcel works and shown by @ebs17.

For others who do not know, pure VBA and Excel worksheets round differently. VBA uses banker's rounding which means it rounds up to the nearest even value when hitting a 5. Excel always rounds up. To Demo

Query2 Query2

dblField	AccessRound	Excel Worksheet Round	EBS	ArnelGP
1.115​	1.12​	1.12​	1.12​	1.11​
1.125​	1.12​	1.13​	1.13​	1.12​
1.135​	1.14​	1.14​	1.14​	1.13​
1.145​	1.14​	1.15​	1.15​	1.14​

In Access Rounding to 2 decimal places.
1.115 rounds up in Access to the nearest even value 1.12
1.125 rounds down in Access to the nearest even value 1.12

In Excel Rounding to 2 decimal places
1.115 rounds up in Excel to 1.12
1.125 rounds up in Excel to 1.13

I believe @arnelgp function answers a different problem which floors it to a specific digit.

On another note. You can use excel worksheet functions in Access VBA. This example is not a place to actually do it because the overhead is a lot and would run extremely slow. However if doing some complex financial, engineering. or stat functions you can do that instead of writing your own function.

Here is the example for the Round function

Private XLapp as object
Public Function XRound(val As Double, Optional Places As Integer = 2) As Double
  If IsNumeric(val) Then
    Dim XLapp As Object
    If XLapp Is Nothing Then Set XLapp = CreateObject("Excel.Application")
    'this will launch a blank copy of excel
    Set XLapp = GetObject(, "Excel.Application")
    XRound = XLapp.WorksheetFunction.Round([val], 2)
  End If
End Function

So you can replace Xlapp.worksheetfunction.Round with the name of any function

 

[Tajaldeen]

Non of these functions has worked at all

 

[MajP]

Non of these functions has worked at all\

Clearly they do. How about you make a little effort and try to write a single coherent sentence, or is that too hard? Obviously they work as advertised, possibly you are unable to communicate your specific issue.