Here are 2 functions that I've used for a few years. One Rounds to the
nearest value you specify, the other allows you to force rounding up/down.
In the VB editor, put them in a General module and then you can use them
pretty much like any other built-in function (from queries, etc.). However,
you can't use user-defined functions as the default property of a field
(AFAIK).
Both came from Access Advisor tips quite a few years ago (as noted). Great
magazine. Subscribe. Don't sue me.
Public Function RoundUpDown( _
varNumber As Variant, varDelta As Variant) As Variant
'***********
'Name: RoundDelta (Function)
'Purpose: round varNumber to varDelta, up or down
'Inputs: varNumber = number to round
' varDelta = rounding precision
' +varDelta = rounds UP
' -varDelta = rounds DOWN
'Example: RoundUpDown(5.12,+0.25) = 5.25
' RoundUpDown(5.12,-0.25) = 5.00
'Output: varNumber rounded UP/DOWN to next varDelta
'Source: Access Advisor Tips 9/2001
'***********
On Error Resume Next
Dim varTemp As Variant
varTemp = CDec(varNumber / varDelta)
If Int(varTemp) = varTemp Then
RoundUpDown = varNumber
Else
RoundUpDown = Int( _
((varNumber + (2 * varDelta)) / varDelta) - 1) _
* varDelta
End If
End Function
Public Function RoundNear(varNumber As Variant, varDelta As Variant) As
Variant
'***********
'Name: RoundNear (Function)
'Purpose: rounds varNumber to the nearest varDelta value
'Inputs: varNumber - number to round
' varDelta - the fraction used as measure of rounding
'Example: RoundNear(53,6) = 54
' RoundNear(1.16,0.25) = 1.25
' RoundNear(1.12,0.25) = 1.00
' RoundNear(1.125,0.25)= 1.25
'Output: varNumber rounded to nearest multiple of varDelta.
'Source: Access Advisor Tips 9/2001
'***********
On Error Resume Next
Dim varDec As Variant
Dim intX As Integer
Dim varX As Variant
varX = varNumber / varDelta
intX = Int(varX)
varDec = CDec(varX) - intX
If varDec >= 0.5 Then
RoundNear = varDelta * (intX + 1)
Else
RoundNear = varDelta * intX
End If
End Function
