How do I install the function MROUND in MS Access?

Question

This function MRound does not exist in MS Access and you can't just install it.

I am trying to use the mround function in an Access report. The function is not listed in the expression builder box of built in functions. When I use the calculation, for example, =mround([AmountDue],0.05), and run the report, it asks for a parameter mround.

score 4 · Answer 1 · answered Nov 03 '19 at 22:25

You could define your own in a public VBA Module, e.g.:

Function MRound(dblNum As Double, dblMtp As Double) As Double
    MRound = dblMtp * Round(dblNum / dblMtp, 0)
End Function

Alternatively, add a reference to the Microsoft Excel Object Library to your VBA Project (Tools > References) and define the MRound function in a public VBA module as:

Function MRound(dblNum As Double, dblMtp As Double) As Double
    MRound = Excel.WorksheetFunction.MRound(dblNum, dblMtp)
End Function

score 1 · Answer 2 · answered Nov 04 '19 at 10:58

There is no MRound present in Access, so create your own rounding function.

However, never use Round for this, as it notoriously buggy. Thus, use plain math and the data type Decimal to avoid errors:

' Rounds a value by 4/5 to the nearest multiplum of a rounding value.
' Accepts any value within the range of data type Currency.
' Mimics Excel function MRound without the limitations of this.
'
' Examples:
'
'   RoundAmount(-922337203685477.5808, 0.05)    -> -922337203685477.6
'   RoundAmount( 922337203685477.5807, 0.05)    ->  922337203685477.6
'   RoundAmount( 10, 3)                         ->                9
'   RoundAmount(-10,-3)                         ->               -9
'   RoundAmount( 1.3, 0.2)                      ->                1.4
'   RoundAmount( 122.25, 0.5)                   ->              122.5
'   RoundAmount( 6.05, 0.1)                     ->                6.1
'   RoundAmount( 7.05, 0.1)                     ->                7.1

' 2009-05-17. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RoundAmount( _
    ByVal Value As Currency, _
    ByVal RoundValue As Currency) _
    As Variant

    Dim BaseValue   As Variant
    Dim Result      As Variant

    BaseValue = Int(Value / CDec(RoundValue) + CDec(0.5))
    Result = BaseValue * RoundValue

    RoundAmount = Result

End Function

This function will, for example, round this correctly:

Amount = RoundAmount( 122.25, 0.5)
Amount -> 122.50

For further information about precision rounding, refer to my project at GitHub:

VBA.Round

and the articles referred to herein.

How do I install the function MROUND in MS Access?

2 Answers2