434,828 Members | 2,241 Online
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
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 someresult = round(numericvalue, decimalplaces) In SQL its use is just Expand|Select|Wrap|Line Numbers SELECT round([fieldname], [decimalplaces]) as calculatedfieldname  FROM sometable Example: to round field Subtotal of table Test to two decimal places: Expand|Select|Wrap|Line Numbers SELECT round([subtotal], 2) As roundedsub FROM test; -Stewart 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 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. -Stewart 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. -Stewart Aug 1 '08 #6