Solved

# Rounding A Calculated Field in an Access Query

Posted on 2011-09-21
368 Views
Hi experts!

Here is the code I'm using for my calculated field:

Percentage: ([quantity]*[makeup])/100

The result currently shows two decimal points.  I would like this field to round to the nearest whole number, no decimals.  Please help!

Thank you.
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 36575988
Try this

Percentage: Round(([quantity]*[makeup])/100 , 0)

mx
0

LVL 75

Expert Comment

ID: 36576004
OR this:

Percentage: FormatNumber(([quantity]*[makeup])/100 , 0)

mx
0

LVL 47

Expert Comment

ID: 36576009
Well, you could do it in the formatting of the field, just set the # of decimals to 0

Or you could use the Round() function:

Percentage: Round([quantity]*[makeup]/100)
0

Author Closing Comment

ID: 36576069
Brilliant!  Amazing the time blown on the small things...I appreciate you!
0

LVL 75

Expert Comment

ID: 36576104
You are welcome.

I actually like FormatNumber() better, because IF ... you need two dec pts, you can't get that with Round() - in some cases:

?Round(55/22,2)
2.5

?FormatNumber(55/22,2)
2.50

mx
0

Author Comment

ID: 36576129
Great tip to add to my plethora of tips, in fact I should create a database for database tips!
0

## Featured Post

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
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.
###### Suggested Courses
Course of the Month7 days, 15 hours left to enroll