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
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
Code:
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
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.