Add a calculated field to your Access form

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:

1. Open the form
   based on the Orders query in Design Mode.
2. Click the text
   box tool in the Database Toolbox.
3. Click and drag
   in the form where you want to display the calculated field.
4. Click and drag
   to select the Text Box Label and enter Total
   Purchase Price.
5. Right-click the Unbound text box.
6. Click
   Properties.
7. 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.