Solved

Access 2003 query, round down to nearest 5 cents

Posted on 2016-10-11
3
27 Views
Last Modified: 2016-10-12
How do I get prices to round down to nearest nickle without using code? Id like to do it from with the query. Here's what Ive tried but it throws the price totally out of whack:

Round(20*products.price+inventoryprofile.estshipcost,0)/20

Thanks.
0
Comment
Question by:QMBB
3 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 41839307
You need to use brackets ( ) to make sure that the multiplication applies to the whole expression, not just the products.price:
Round(20*(products.price+inventoryprofile.estshipcost),0)/20

Open in new window

0
 

Author Closing Comment

by:QMBB
ID: 41839316
Woo hoo! Thanks.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41839631
No Woo Hoo, as this code doesn't round down but rounds to the nearest even.
Further, Round is buggy.

To round down, you need this:

    ListPrice: Int(20*(products.price+inventoryprofile.estshipcost))/20

If you have a serious need for rounding, study the options here:

Rounding values up, down, by 4/5, or to significant figures

/gustav
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now