Creating Forms  «Prev 

Add Calculated Control in Access

You can use calculated controls 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.
This procedure helps you create a calculated control without using a control wizard.
  1. Right-click the form or report in the Navigation Pane, and then click Design View Button image.
  2. On the Design tab, in the Controls group, click the tool for the type of control you want to create.
    Access Calculation Contorl

    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.
  3. 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.
  4. If a control wizard starts, click Cancel to close it.
  5. 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 Builder button next to the Control Source property box.
  6. Switch to Form view or Report view and verify that the calculated control works as you expect.

1) The tabular form is actually a subform to a form that shows project data. I want to add a column that shows how much I can bill for each date. To do so, I have to display the form in design view.

2) Once the form is in design view, I need to add a new text box by using the Text Box button in the toolbox. I click the Text Box button and draw the new control on the form.

3) I get a text box and an accompanying label. I will clean it up a bit before adding the expression by moving the label into the form header and making the size and position of the new controls match those already on the form. I will also change the text in the label from the Text6 to Billable.

4) Now I am ready to put the expression in the text box. The billable amount is the hours worked times the $50 hourly rate, so the expression is :
=[Hours worked] * 50. The expression must start with an equals sign, and field names need to be in square brackets. Remember the rules for query expressions

5) The text box does not need to be large enough to display the whole expression as long as it is all in there. Now I am ready to see the result.

6) The result is show in the form view.