Rounding up to the nearest 0.25 in Access
Posted on 2014-07-16
I would like to be able to round up to the nearest 0.25 in Access. The number I am rounding is always positive btw. I have searched this into the ground and thought I had an answer...but something is wrong and I have no idea what to change to make it work. I added the following module to my database...
Function RoundToNearest(dblNumber As Double, varRoundAmount As Double, _
Optional varUp As Variant) As Double
Dim dblTemp As Double
Dim lngTemp As Long
dblTemp = dblNumber / varRoundAmount
lngTemp = CLng(dblTemp)
If lngTemp = dblTemp Then
RoundToNearest = dblNumber
If IsMissing(varUp) Then
' round up
dblTemp = lngTemp + 1
' round down
dblTemp = lngTemp
RoundToNearest = dblTemp * varRoundAmount
I then put the following in my query...RoundToNearest([MyNumber],0.25).
2.6616 as 3.00 (should be 2.75)
2.9683 as 3.25 (should be 3.00)
2.7921 as 3.00 (ok)
2.6019 as 2.75 (ok)
3.2427 as 3.50 (should be 3.25)
2 out of 5 just doesn’t work. What do I need to change or is there another way?
Thank you in advance...this one is hurting my head!!