Solved

# Rounding up to the nearest 0.25 in Access

Posted on 2014-07-16
Medium Priority
567 Views
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
Else
If IsMissing(varUp) Then
' round up
dblTemp = lngTemp + 1
Else
' round down
dblTemp = lngTemp
End If
RoundToNearest = dblTemp * varRoundAmount
End If
End Function

I then put the following in my query...RoundToNearest([MyNumber],0.25).

It delivers...
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?

0
Question by:KJKAccess
• 2
• 2
• 2
• +2

LVL 120

Expert Comment

ID: 40200811
try
round(yourNumber * 4,0)/4
0

LVL 22

Expert Comment

ID: 40200818
Here's another formula you can try:

=INT(4*[YourNumberField]+0.5)/4

Flyster
0

LVL 31

Expert Comment

ID: 40200842
To get the results you specified, where the value is higher than the input number (A1 is your number)

=ROUND(A1*4+0.5,0)/4
0

LVL 52

Accepted Solution

Gustav Brock earned 2000 total points
ID: 40201289
This works:

dblRoundedUp = -Int(-dblValue * 4) / 4
0

LVL 31

Expert Comment

ID: 40201411
Tested Gustav's comment. I prefer it as a solution. My comment fails for .5 value.
0

Author Closing Comment

ID: 40201974
Thanks!  I waited way too long to ask for help.  This is awesome!!
0

LVL 120

Expert Comment

ID: 40202001
KJKAccess,

did my post at http:#a40200811 failed your test?
0

Author Comment

ID: 40202102
"Round" goes in which ever direction is closer...
So 2.76 would round to 2.75...but I need it to round up in all instances (go to 3.00 for this example)
0

LVL 52

Expert Comment

ID: 40202120
You are welcome!

/gustav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month14 days, 15 hours left to enroll