Microsoft Access 2010 Bible

Chapter 4: Selecting Data with Queries

147

Access still considers the State field because it contains the query’s criteria, but otherwise the

field is ignored.

A common reason to hide a field in the query is because the field is used for sorting, but its value is

not needed in the query. For example, consider a query involving the invoices from the Collectible

Mini Cars database. For a number of reasons, the users might want to see the invoices sorted by

the order date, even though the actual order date is irrelevant for this particular purpose. Simply

include the OrderDate field in the QBE grid, set the sort order for the OrderDate field, and

uncheck its Show box. Access sorts the data by the OrderDate field even though the field is not

shown in the query’s results.

Caution
If you save a query that has an unused field (its Show box is unchecked and no criteria or sort order is applied
to the field), Access eliminates the field from the query as part of the query-optimization process. The next
time you open the query, the field won’t be included in the query’s design.

Changing the Sort Order

When viewing a recordset, you often want to display the data in a sorted order. You might want to

sort the recordset to make it easier to analyze the data (for example, to look at all the tblProd-

ucts sorted by category).

Sorting places the records in alphabetical or numeric order. The sort order can be ascending (0 to 9

and A to Z) or descending (9 to 0 and Z to A). You can sort on a single field or multiple fields.

You input sorting directions in the Sort row in the QBE grid. To specify a sort order on a particular

field (such as LastName), perform these steps:

1. Position the cursor in the Sort cell in the LastName column.


2. Click the drop-down list that appears in the cell, and select the sort order (Ascending or
   Descending) you want to apply.

Figure 4.18 shows the QBE grid with ascending sorts specified for the LastName and
FirstName fields. Notice that the LastName field is still showing the sort options avail-
able. Also notice that the word Ascending is being selected in the field’s Sort cell.

Note
You can’t sort on a Memo or an OLE object field.