Arranging Data

Often, the most efficient way to order the data in your Recordset is by specifying an ORDER BY clause in the SQL command used to return results to it. However, you might need to change the order of the data in a Recordset that has already been created. You can use the Sort property to establish the order in which rows of a Recordset are traversed. Furthermore, the Filter property determines which rows are accessible when traversing rows.

The Sort property sets or returns a String value that indicates the field names in the Recordset on which to sort. Each name is separated by a comma and is optionally followed by a space and the keyword ASC (which sorts the field in ascending order) or DESC (which sorts the field in descending order). By default, if no keyword is specified, the field is sorted in ascending order.

The sort operation is efficient because data is not physically rearranged but is simply accessed in the order specified by an index.

The Sort property requires the CursorLocation property to be set to adUseClient. A temporary index will be created for each field specified in the Sort property if an index does not already exist.

Setting the Sort property to an empty string will reset the rows to their original order and delete temporary indexes. Existing indexes will not be deleted.

Suppose a Recordset contains three fields named firstName, middleInitial, and lastName. Set the Sort property to the string "lastName DESC, firstName ASC", which will order the Recordset by last name in descending order and then by first name in ascending order. The middle initial is ignored.

No field referenced in a sort criteria string can be named "ASC" or "DESC" because those names conflict with the keywords ASC and DESC. Give a field with a conflicting name an alias by using the AS keyword in the query that returns the Recordset.

For more details about Recordset filtering, see Filtering the Results.