This article aims to help you understand how, when, and where to make use of the Round( ) function, what is the basic syntax, and how can you make changes to it, as per your requirements
MS Access database is the kind of application that can prove to be a great business solution for almost all types of business. There are a plenty of functions in the application that can come to your rescue on different occasions. One such function we are going to discuss today is the Rounding () function in MS Access. This function, as the name suggests, helps you round of the given number to a mentioned amount of decimal places. This rounding feature became a built in feature only in Access 2000, and the editions thereafter. In earlier editions, it did not exist.
Before we proceed towards using this feature, and going deeper in it, we need to understand its basic syntax, and how to put it to use.
Round (expression [,numdecimalplaces] )
From the above given syntax, the word ‘Round’ will be copied as it is, before the given expression, you insert bracket, before closing the bracket, you need to enter the decided decimal places to which you wish to round off the value. This is optional, and can be skipped. If you do not mention the number after the decimal point, it will be rounded off to the nearest whole number.
Built in Function
As mentioned above, in the MS Access edition of 2000 and later, the Rounding ( ) function was built in, it is present in the text box control source, and might also be found in any of the calculated query field. For any given expression in Field Row of Query Design, you only need to insert ( ), to round it off. For example, if you are a seller, and need to calculate the total cost of one single product for all its quantity, your expression would be:
Total Cost = [Cost of one] * [Total pieces]
If you wish to round off the given expression, you would change it to:
Total Cost = Round ( [Cost of one] * [Total pieces] )
The above rounded of expression can further be changed to:
Total Cost = Round ( [Cost of one] * [Total pieces], 2 )
In the above expression we have specified that we want the number to be rounded off to only two numbers after the decimal point.
If you want to round the given values to the lower number, you make use of the Int( ) function.
The syntax here will be: Int ([field])
For rounding down to lower cent, you round the expression, multiply it by hundred and then divide it by 100. Changing the syntax to:
Int (100 * [field] / 100)
When rounding down negative values, use Fix( ) and not Int( ), to round off the number to zero.
For rounding up towards highest number, make the syntax negative.
Example: -Int ( - [field] )
This will round the given number upwords.
For rounding up towards the higher cent, follow the same syntax for rounding down to lower cent, but with a ‘-’ sign.
Example: Int (-100 * [field]) / -100
You can round up the given number to any value of your choice, by making the required changes in the syntax, ensuring that the basic requirements are maintained.
MS Access like any other database is open to faults. Hence if you are running a Line of Business application on Access, consider keeping a corrupt accdb recovery tool nearby to deal with untoward incidents of Access corruption.
Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including damaged mdf and excel recovery software products. For more information visit www.datanumen.com