Access VBA Sort: Forms, Queries, and Recordsets

To sort records in an Access form with VBA, set the form’s OrderBy property and turn sorting on: for example, Me.OrderBy = "[LastName] ASC, [FirstName] ASC" followed by Me.OrderByOn = True. For a DAO recordset, put an ORDER BY clause in its SQL; sorting changes the order in which records are displayed or read, not their stored order in the table.

How sorting works in Access

Access tables do not have a guaranteed, permanent row order. A table may appear in one order when opened and a different order after an edit, an index change, or a query plan change. If a particular sequence matters, specify it when retrieving or displaying the records. In Access SQL, that usually means an ORDER BY clause; in a form, it can mean the form’s OrderBy property.

Sorting is also different from filtering. A filter determines which records are included; a sort determines their sequence. You can use both at once. Sorting is not the same as grouping, either: grouping collects records into sections, especially in reports, while sorting orders records within or between those sections.

Choose the method based on what the user will see or what your VBA code will process:

Use a form’s OrderBy and OrderByOn properties to arrange records displayed by that form.
Use SQL with ORDER BY when opening a query or DAO recordset in a known sequence.
Configure a report’s sort and group levels for report output.

Sort a form with VBA

A form’s OrderBy property contains the sort expression, and OrderByOn controls whether Access applies it. The expression can name one or more fields from the form’s record source. Use ASC for ascending order and DESC for descending order. The following code can go in the form’s module behind a command button:

Private Sub cmdSortByName_Click()
    ' Sort the form's displayed records by last name, then first name.
    Me.OrderBy = "[LastName] ASC, [FirstName] ASC"
    Me.OrderByOn = True
End Sub

To remove the active sort, turn OrderByOn off. The text in OrderBy may remain as a setting, but Access will not apply it while sorting is off:

Private Sub cmdClearSort_Click()
    ' Stop applying the form's current sort.
    Me.OrderByOn = False
End Sub

Put the code in the form’s class module, not a standard module, because Me refers to the form instance. The named fields must be available from the form’s record source. If a field is absent from the underlying query, add it to that query or use a field that is already included. A control’s label is not necessarily the name of the underlying field, so check the control’s Control Source and the query’s field names when a sort fails.

For a sort that should be applied whenever the form opens, you can set the properties in the form’s Load event, or configure the form’s Order By property in Design View and enable Order By On Load. The event approach is useful when the sort depends on a condition or user choice. A form’s sort affects that form’s displayed records; it does not rearrange the source table for every other query or form.

Sort a DAO recordset with SQL

For a DAO recordset opened from a query, use SQL to define the order before opening it. This example reads customer records in ascending last-name and first-name order, with the ID as a final tie-breaker:

Public Sub ListCustomersInOrder()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sqlText As String

    Set db = CurrentDb
    sqlText = "SELECT CustomerID, LastName, FirstName " & _
              "FROM Customers " & _
              "ORDER BY LastName ASC, FirstName ASC, CustomerID ASC;"

    Set rs = db.OpenRecordset(sqlText, dbOpenSnapshot)

    ' Read records here, then release the objects.
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Replace the table and field names with names in your database. A snapshot is appropriate for read-only navigation; use a different recordset type when your task requires editing and that type is suitable. Modern Access installations normally include the DAO library reference, but a missing DAO reference can cause a compile error. Check the project’s references before changing code or adding another library.

SQL is generally the clearest choice for ordering a dynaset or snapshot. DAO’s Sort property is intended for table-type recordsets, which are limited to local tables; it is not a general substitute for adding ORDER BY to a query. If the code needs a sorted set from a query or linked data source, write the ordering into the SQL rather than assuming a recordset property will reorder it.

Sort by several fields and make ties predictable

Separate sort fields with commas. Access sorts by the first field, then uses the next field to order records tied on the first, and continues through the list. For example, a sales query could sort by region, then newest order date, then order number:

SELECT OrderID, Region, OrderDate
FROM Orders
ORDER BY Region ASC, OrderDate DESC, OrderID ASC;

This places each region alphabetically, orders its records from most recent date to oldest, and uses the order ID to settle records sharing a date. That last field matters when the order needs to be repeatable. If two rows have identical values in every specified sort field, Access has no instruction for which one should appear first; their relative order is not guaranteed. Add a unique key as a final criterion when downstream code or a person relies on a consistent sequence.

For a descending numeric sort, use DESC after the field name. For a mixed sort, specify the direction separately for each field. You can sort a date descending while keeping a name ascending, for example. Be careful not to assume that sorting by a displayed value is identical to sorting by the value behind it: a calculated display expression, formatted date, or text representation can produce a different order than the original field.

Choose the right place to sort

Place sorting as close as practical to the data operation that needs it. If a form’s users need to change the display order, use the form properties or a saved query as the form’s record source. If a VBA procedure reads records in sequence, put ORDER BY in the SQL used to open the recordset. That makes the requirement visible in the query and avoids relying on how a table happens to look in Datasheet view.

For a report, use its Group & Sort settings to control report ordering and grouping. A report may have its own sorting and grouping configuration, so do not assume that the order you saw in a table or a form will be the report’s final order. If the report is based on a saved query, an ORDER BY clause can communicate the intended sequence, but review the report’s own group and sort levels as well.

Datasheet sorting through the Access interface is convenient for a quick inspection, but it is not a reliable way to define the order for later code. Likewise, indexes can help Access find and sort records efficiently, but an index does not make an unspecified query order a permanent contract. Request the order explicitly wherever it matters.

Common sorting problems

The form does not move into the expected order: confirm that OrderByOn is set to True, the form is bound to the expected record source, and the field names in OrderBy exist there.
Numbers sort like words: a field stored as Short Text sorts alphabetically, so values such as 1, 10, and 2 may appear in that sequence. Use a numeric field for numeric data; converting text values is only safe when they consistently contain valid numbers.
Names or dates appear out of order: check the data type and the actual stored value. A date saved as text can sort differently from a true Date/Time field, and formatted display text may not represent the underlying value.
Rows with matching values change places: add another sort field, preferably a unique key, to resolve ties.
A sort expression causes an error: bracket field names that contain spaces or reserved words, and verify that they are fields in the record source. For instance, use [Order Date] rather than an unbracketed name containing a space.

When allowing users to pick a sort field, do not paste arbitrary text from a text box into an SQL statement or an Order By expression. Instead, map each permitted choice to a fixed, known field name and direction. This avoids malformed expressions and limits the sort to fields the application is intended to expose. Sorting does not normally change table data, but VBA procedures can do more than sort. Save a copy of the Access database before running a macro; actions performed by VBA cannot be undone with Ctrl+Z.

Frequently Asked Questions

Can I permanently sort an Access table with VBA?

No. A table does not have a guaranteed physical row order that can serve as its permanent display order. Store the data, then request a sequence with a query’s ORDER BY clause or a form’s sort settings whenever you need to see or process it in that order.

How do I sort a form in descending order?

Set the form’s OrderBy property to a field followed by DESC, then set OrderByOn to True. For example, Me.OrderBy = "[OrderDate] DESC" sorts the form’s records from newest to oldest when OrderDate is a date field.

Why does Access sort 1, 10, 2 instead of 1, 2, 10?

The values are likely stored as text. Text is sorted alphabetically, whereas a numeric field sorts by numeric value. Check the field’s data type and, if the data is genuinely numeric, store it as a suitable numeric type rather than relying on text sorting.

Does sorting a form change the table or other forms?

No. A form’s Order By setting controls the order shown by that form. It does not rewrite rows or automatically impose the same order on another form, query, or report. Define the sort separately where it is needed.

Should I use DAO Sort or SQL ORDER BY?

Use SQL ORDER BY for the query or recordset you are opening, especially for dynasets and snapshots. DAO’s Sort property is limited to table-type recordsets, so it is not the general solution for sorting query results.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.