Add a calculated field to your Access form

by Mary Ann Richardson | Apr 24, 2007 7:00:00 AM

Tags: Microsoft Office, Databases, Mary Ann Richardson, calculated field, Microsoft Access, Total Purchase Price, Microsoft Office Suite Access Tips Newsletter

4 comment(s)

Takeaway: Need to add a calculated field to an Access form? It's easy, and you can do it on the fly. Just add a new text box control with the appropriate formula as its control source.

If you are building an Access form based on a query and notice you will need to add a calculated field, you don't need to redesign your query -- just add the calculated field to your form. For example, say you are creating a form based on the Orders query, which consists of fields from the Orders and Order Details tables. You notice the query does not have a calculated field for the Total Purchase Price (Quantity*UnitPrice), which you want to appear on your form. Follow these steps to add a field for this calculation:

Open the form based on the Orders query in Design Mode.
Click the text box tool in the Database Toolbox.
Click and drag in the form where you want to display the calculated field.
Click and drag to select the Text Box Label and enter Total Purchase Price.
Right-click the Unbound text box.
Click Properties.
Under the Data tab, click in the Control Source text box and enter the following formula:
```
=IIf(IsNull([Quantity]*[UnitPrice]),0,([Quantity]*[UnitPrice]))
```

As you enter orders into the form, the Total Purchase Price will display in the calculated field.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

4 comment(s)