ROUND Worksheet function

[vsts2007]

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

[Stewart Ross]

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:

someresult = round(numericvalue, decimalplaces)

In SQL its use is just

SELECT round([fieldname], [decimalplaces]) as calculatedfieldname 
FROM sometable

Example: to round field Subtotal of table Test to two decimal places:

SELECT round([subtotal], 2) As roundedsub FROM test;

-Stewart

[vsts2007]

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];

[Stewart Ross]

OK, taking just one of your IIFs as an example and rounding to two decimal places:

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

[vsts2007]

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

[Stewart Ross]

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