?
Solved

Rounding up to the nearest 0.25 in Access

Posted on 2014-07-16
9
Medium Priority
?
567 Views
Last Modified: 2014-07-17
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?

Thank you in advance...this one is hurting my head!!
0
Comment
Question by:KJKAccess
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40200811
try
round(yourNumber * 4,0)/4
0
 
LVL 22

Expert Comment

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

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

Flyster
0
 
LVL 31

Expert Comment

by:hnasr
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 52

Accepted Solution

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

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

Expert Comment

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

Author Closing Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40202001
KJKAccess,

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

Author Comment

by:KJKAccess
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

by:Gustav Brock
ID: 40202120
You are welcome!

/gustav
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question