---

MS Access 2003: Sort a form's records based on the value of a calculated field

This MSAccess tutorial explains how to sort a form's records based on the value of a calculated field in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I have a calculated field on a form which displays the number of records in the Account_units table for the account currently displayed in the Account form.

How can I sort the records in the form based on the value in this calculated field?

Answer: Please note that this example will only work with an Access 2000 database or higher. You will need to start with a new Access 2000 file, not one that has been converted from a previous Access version.

To sort the records in the form based on a calculated field, you need to add the calculated field to the result set in the Record Source property. We've provided a download to demonstrate how this can be done.

Download version in Access 2000

We've created a form called frmAccounts that lists all records in the Accounts table. We've then created a textbox called TotalUnits that displays the number of records in the Account_units table (for the account displayed on frmAccount).

Open the form in Design view and select Properties under the View menu. Select the property called Record Source. A button with three dots should appear to the right of this property. Click on this button.

When the Query Builder appears, select all of the fields from the Accounts table and then enter the function for the calculated field. In this example, we've entered the following:

CalcUnits([AcctNo])

Next, select how you've like to sort the calculated field. We've chosen to sort in Descending order.

You can close down the Query Builder. When prompted with the message box, "Do you want to save the changes made to the SQL statement and update the property?", click on the Yes button.

Now when you open the form, you'll see the form records sorted in descending order based on the "Total Units" field.