By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,828 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,828 IT Pros & Developers. It's quick & easy.

ROUND Worksheet function

P: 56
Hi all,
i need help regarding one worksheet function: can we use ROUND worksheet function in access also, i need suggestions (how to use)
plz help me
Jul 30 '08 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 2.5K+
P: 2,545
Hi. Access VBA supplies Round as an intrinsic function, and you should be able to use it anywhere you can use an expression (in a query, in VBA code etc).

It's syntax is identical to its Excel counterpart:

Expand|Select|Wrap|Line Numbers
  1. someresult = round(numericvalue, decimalplaces)
In SQL its use is just

Expand|Select|Wrap|Line Numbers
  1. SELECT round([fieldname], [decimalplaces]) as calculatedfieldname 
  2. FROM sometable
Example: to round field Subtotal of table Test to two decimal places:

Expand|Select|Wrap|Line Numbers
  1. SELECT round([subtotal], 2) As roundedsub FROM test;
Jul 30 '08 #2

P: 56
sorry i am unable to get it
here i am giving the code
can u suggest me how to do it:

SELECT [Payslip ].[EmployeeNo], [Payslip ].[Name of the Member], [Payslip ].[Bank Number], [Payslip ].[other arrears], [Payslip ].[Basic Pay], [Payslip ].[PFapplicable],
IIf([PFapplicable]="yes",([Basic Pay]*0.5)) AS DP, ---- ROUND THIS FIGURE
IIf([PFapplicable]="yes",(([Basic Pay]+[dp])*0.47)) AS DA, ---- ROUND THIS FIGURE
IIf([PFapplicable]="yes",(([basic pay]+[DP])*0.3)) AS HRA, ---- ROUND THIS FIGURE
IIf([PFapplicable]="yes",(([Basic Pay]+[dp]+[da]+[basic arrears]+[dp arrears]+[da arrears])*0.12)) AS PF, ---- ROUND THIS FIGURE
FROM [Payslip];
Aug 1 '08 #3

Expert Mod 2.5K+
P: 2,545
OK, taking just one of your IIFs as an example and rounding to two decimal places:

Expand|Select|Wrap|Line Numbers
  1. IIf([PFapplicable]="yes",Round(([Basic Pay]*0.5), 2)) AS DP
The others are done the same way.

Please note that rounding is generally done as the final step in data presentation - i.e. at the reporting stage (or on a form presented to a user). Rounding in a query would not normally be necessary unless the data is to be exported "raw" for use by users in some other application.

Rounding data too early can lead to the accumulation of small rounding errors in totals and so on if further calculations are carried out; it is normal to retain as many significant figures as possible for calculation purposes and to round the final result only.

If you are printing the data using a report you can always format the controls to present the number of decimal places required - the format selected will apply rounding to the value shown automatically.

Aug 1 '08 #4

P: 56
I tried it in query but I am unable to see the report of it.
it showing some error
if i want to write the code in form or in report whats the process
sorry for the trouble i am giving u
i am learning access recently
plz provide me needed help
thank u
Aug 1 '08 #5

Expert Mod 2.5K+
P: 2,545
Hi. To format a control on a form or a report to show a rounded value open the form or report in design view, select each control. If the control properties tab is not showing right click on the control, then select properties to display the properties tabs. Select the Format property and set to Fixed. Select decimal places and set to 2 (or 1 or 0 or whatever is the number of places you wish to display).

I'm sorry to say that I doubt if I will be able to assist you further with this issue, as there is no more I can do for you without having the database in front of me. I have done all I can to ensure that you understand that rounding is one of the last tasks undertaken, and that it is a presentation issue resolved by using fixed decimal places for your controls as mentioned above.

Aug 1 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.