How to Use Calculations in Forms with Microsoft Access

A Text Box form control creates an unbound control that is unlinked to any data source with a Label and Text Box. This can be used to perform calculations and labelling the resulting calculation.

Bound form controls obtains data from a source or a field. Unbound form controls are not linked to a data source and are calculated. Unbound form controls are calculated using other information from the database.

Calculations can use any of the mathematical symbols (+, -, *, /) and expressions such as Average, Sum, Max and Min.

1. Download the work files associated with Advanced Database at www.digidiscover.com/downloads

2. Open the Computer Store database

3. Open the Products Report report

4. Switch to Design View and enlarge the Detail area

5. Click on the Text Box button on the Design tab in the Controls group

6. Create an unbound control beneath Items Sold

7. In the Tools group, select Property Sheet to display the control’s properties

8. Click on the Data tab on the Property Sheet pane and select Control Source. Choose Product as the Control Source

9. Enter the following expression into the textbox:

=[Price]*[Items Sold]

10. Name the label Product Total

11. Resize the label and text box so it aligns with the other text boxes

12. Click inside the text box and on the Property Sheet pane select the Format tab

13. Change the format to Euro

14. Switch to Form View to see the result

15. Return to Design View. Add another text box beneath the Product Total

16. Enter in the following expression:

=IIf([Product_Total]>1000,“High Value”,“Low Value”)

17. This expression will show the text “High Value” if the product total is above €1,000 and display “Low Value” if the product total is lower than €1,000

18. Enter a label of Product Value

19. Save the form and close it

To learn more about advanced database features check out the Advanced ICDL Databases paperback: