You can use calculated controls on forms and reports in Access databases to display the results of a calculation. For example, if you have a report that displays the number of items sold and the price of each unit, you can add a calculated text box that multiplies those two fields to display the total price. The Control Source property of the calculated text box contains an expression that multiplies two fields (the number of items times the unit price) to obtain the result.
What do you want to do?
Create a calculated control
This procedure helps you create a calculated control without using a control wizard.
-
Right-click the form or report in the Navigation Pane, and then click Design View.
-
On the Design tab, in the Controls group, click the tool for the type of control you want to create.
For a list of control types that can be used as calculated controls, see the section Learn which types of controls can be used as calculated controls.
-
Position the pointer where you want the control to be placed on the form or report, and then click on the form or report to insert the control.
-
If a control wizard starts, click Cancel to close it.
-
Select the control, press F4 to display the property sheet, and then type an expression in the Control Source property box. To use the Expression Builder to create the expression, click
-
Switch to Form view or Report view and verify that the calculated control works as you expect.
Notes:
-
Precede each expression with the = operator. For example: =[UnitPrice]*.75.
-
For more information about creating expressions, see the article Learn to build an expression.
-
If you need more room to type an expression in the Control Source property box, press SHIFT+F2 to open the Zoom box.
-
If your form or report is based on a query, you might want to put the expression in the query instead of in a calculated control. Doing this can improve performance and, if you are going to calculate totals for groups of records, it is easier to use the name of a calculated field in an aggregate function.
-
When you sort on a calculated control in a form or report, ensure that the Format property of the control is set appropriately. Otherwise, calculated numeric or date values might sort alphabetically instead of numerically.
-
Change a bound control to a calculated control
A good way to create a calculated control is by first creating a bound control (for example, by dragging a field from the Field List pane to your form or report), and then editing the bound control's Control Source property to create an expression. This works well, as long as you ensure that the name of the control does not conflict with any of the field names contained in the expression. For more information about creating a bound control by dragging a field from the Field List pane, see the article Add a field to a form or report.
Assuming you have already created your bound control, use the following procedure to change it to a calculated control and avoid any name conflicts.
-
Right-click the form or report in the Navigation Pane, and then click Design View.
-
Click the field you want to change, and then press F4 to open the property sheet.
-
On the All tab of the property sheet, if the Name property matches the Control Source property, edit the Name property so that the two properties do not match. Ensure that the name you enter is not a reserved word or the name of another field in your database. For more information about reserved words, see the article Learn about Access reserved words and symbols.
-
Edit the string in the Control Source property box so that it contains the expression you want.
-
Press CTRL+S to save your changes.
-
Switch to Layout view or Form view and verify that the calculated control works as you expect.
If the control does not display the data you want (for example, if Access displays #Name? in the control), check the record source of the form or report to ensure that all the fields you used in the expression are available. If the record source is a query, you might need to add one or more fields to the query before the expression will work.
Notes:
-
Precede each expression with the = operator. For example: =[UnitPrice]*.75.
-
If you need more room to type an expression in the Control Source property box, press SHIFT+F2 to open the Zoom box.
-
If your form or report is based on a query, you might want to put the expression in the query instead of in a calculated control. Doing this can improve performance and, if you are going to calculate totals for groups of records, it is easier to use the name of a calculated field in an aggregate function.
-
When you sort on a calculated control in a form or report, ensure the Format property of the control is set appropriately. Otherwise, calculated numeric or date values may sort alphabetically instead of numerically.
Learn which types of controls can be used as calculated controls
Text boxes are the most popular choice for a calculated control because they can display so many different types of data. However, any control that has a Control Source property can be used as a calculated control. In many cases, it doesn't make sense to use a certain control type as a calculated control, because you can't update that control the way you can update a bound or unbound control. For example, if you place a check box control on a form and then enter an expression in the Control Source property of the check box, you can no longer select or clear the check box by clicking it. The check box appears selected or cleared, based on the results of the expression. If you click the check box, Access displays an alert on the Access status bar: Control can't be edited; it's bound to the expression <your expression>. On a report, however, it may be useful to base a check box control on the results of a calculation, because controls on reports are used only to display information.
