Content
Resources
To Do
Toolbox

 RoundToNearest

## RoundToNearest() Function

Return a ceiling or floor value of the passed value to the nearest interval.

## Syntax

CODE
RoundToNearest(dblValue, [dblInterval], [blUseCeiling])

Where dblValue is value you wish to round; dblInterval is the interval you wish to round the value to, the default is 1; blUseCeiling if set to True, the returned value is the "next" interval value, if False, the value returned in the 'previous" interval value (the default is True).

CODE
Public Function RoundToNearest(dblValue As Double, _
Optional dblInterval As Double = 1, _
Optional blUseCeiling As Boolean = True) As Double
'Returns the ceiling or floor of the passed value to the nearest
'interval.  blUseCeiling of False will coerce to the interval value
'less than the passed value, blUseCeiling of TRUE (Default) will
'coerce to the interval value greater than the value passed.
'
' http://www.utteraccess.com/wiki/index.php/RoundToNearest
' Code courtesy of UtterAccess Wiki
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date        brief descripton
' 1.0  2013-03-11  Initial Release
'

If dblInterval <> 0 Then
If blUseCeiling = True Then
RoundToNearest = -Int(-dblValue / dblInterval) * dblInterval
Else
RoundToNearest = Int(dblValue / dblInterval) * dblInterval
End If
Else
RoundToNearest = 0
End If

End Function

Double.

## Example

From the Immediate Window:

CODE

? RoundToNearest(38125,500,False)
38000

? RoundToNearest(38625,500,False)
38500

? RoundToNearest(38125,500,True)
38500

? RoundToNearest(38625,500,True)
39000