Solved

Rounding up in Access query

Posted on 2011-03-11
4
274 Views
Last Modified: 2012-05-11
Hello,

I need a query that takes a price and rounds UP to the next 5p, i.e £2.36 should go to £2.40 and £5.12 should go to £5.15 etc.

Thanks
0
Comment
Question by:Petermcg001
  • 2
4 Comments
 
LVL 6

Expert Comment

by:zane_o
ID: 35109202
Rounding to 5p is done by multiplying the number by 20, rounding it, and dividing by 20 to get to the proper value.
    Round(20 * [Amount], 0) / 20
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 35109211
Try this - http://allenbrowne.com/round.html

Rounding up
To round upwards towards the next highest number, take advantage of the way Int() rounds negative numbers downwards, like this:
    - Int( - [MyField])

As shown above, Int(-2.1) rounds down to -3. Therefore this expression rounds 2.1 up to 3.

To round up to the higher cent, multiply by -100, round, and divide by -100:
    Int(-100 * [MyField]) / -100
0
 
LVL 6

Accepted Solution

by:
zane_o earned 500 total points
ID: 35109286
Missed the always round up part. If you combine the two responses here, you get something like:
 Int(-20 * [Amount]) / -20
0
 
LVL 1

Author Comment

by:Petermcg001
ID: 35109524
Thanks for looking at this.  zane o's solution works perfectly.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

786 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