alex said:
Hello,
Is anyone familiar with a SQL code/statement in Access which would
replicate Excel's MROUND function? I.e., how to round a number to the
nearest multiple.
alex
Try this:
'---Begin Module code-----
Public Function MRound(Number As Double, Multiple As Integer) As String
MRound = "#Num"
If (Number >= 0 And Multiple <= 0) Or Multiple = 0 Then Exit Function
MRound = Round(Number / Multiple, 0) * Multiple
End Function
'Use Access' Round function (not in A97) or any of your favorites
Public Function Round(varIn As Variant, intPlaces As Integer) As Variant
Round = Int(10 ^ intPlaces * varIn + 0.5) / 10 ^ intPlaces
End Function
'-----End Module code-----
Example:
SELECT MyField, CDbl(MRound([MyField], 3)) AS MyFieldRoundedTo3s FROM
MyTable;
I'm being hyper literal with the function. Excessive literalness is an
occupational hazard for programmers
. MRound and the "Number"
argument can be changed to Variants. If "Number" is Null, set MRound to
Null.
Public Function MRound(Number As Variant, Multiple As Integer) As Variant
MRound = Null
If IsNull(Number) Or (Number >= 0 And Multiple <= 0) Or Multiple = 0
Then Exit Function
MRound = Round(Number / Multiple, 0) * Multiple
End Function
Then:
SELECT MyField, MRound([MyField], 3) AS MyFieldRoundedTo3s FROM MyTable;
or even:
SELECT MyField, IIf(MRound([MyField], 3) IS Null, Null,
CDbl(MRound([MyField], 3))) AS MyFieldRoundedTo3s FROM MyTable;
James A. Fortune
(e-mail address removed)
