VBA Access: Sort Records in Queries and Forms

VBA & macros · Updated November 18, 2026

To sort records with Access VBA, put an ORDER BY clause in the SQL used to open a query or recordset; for a form, set its OrderBy property and turn on OrderByOn. For example, SQL can sort employees by last name and then first name, while a form can apply the same order to the records it displays.

Choose where the sorting should happen

The right method depends on what you want sorted. A query or SQL statement is usually the clearest choice when code opens records for processing, exports, or another operation. A form’s sort properties are more appropriate when the goal is to change the order users see on screen. Both methods order a set of records; neither creates a guaranteed physical row order inside a table.

Access tables store records without a dependable presentation order. A table may appear in a familiar order because of a primary key, an index, or the way Access currently displays it, but code should not rely on that appearance. Specify a sort whenever order matters. Also distinguish sorting from filtering: a sort changes sequence, while a filter limits which records are included.

How SQL ORDER BY works

In Access SQL, ORDER BY follows the FROM clause and names one or more fields. Use ASC for ascending order and DESC for descending order. Ascending is the default, but writing it explicitly can make code easier to read. Put field names in square brackets when they contain spaces or could be confused with a reserved word.

SELECT [EmployeeID], [LastName], [FirstName], [HireDate]
FROM [Employees]
ORDER BY [LastName] ASC, [FirstName] ASC;

This statement sorts first by last name. When multiple employees have the same last name, it sorts those records by first name. If those fields can also tie and you need a repeatable order, add a unique field such as [EmployeeID] as the final sort key. Without a unique tie-breaker, records with identical sort values can appear in either relative order.

Sort fields do not have to be displayed in the query result, although they must be available to the query. Sorting is based on the field’s data type: a numeric field sorts numerically, while a text field sorts as text. If numbers were stored as text, values such as 100 and 20 may not appear in the numeric order a reader expects. Correcting the field type or sorting by a valid numeric expression is better than assuming text will behave like a number.

Open a sorted DAO recordset

A common VBA task is to read rows in a particular order. Build the SQL with its ordering clause, then open it as a DAO snapshot when you only need to inspect records. A snapshot is read-only, which is suitable for reporting or looping through results. The following example assumes a table named Employees with the four fields shown.

Public Sub ListEmployeesByName()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    ' Open a read-only result sorted by surname, then given name.
    Set db = CurrentDb
    Set rs = db.OpenRecordset( _
        "SELECT [EmployeeID], [LastName], [FirstName], [HireDate] " & _
        "FROM [Employees] " & _
        "ORDER BY [LastName] ASC, [FirstName] ASC, [EmployeeID] ASC;", _
        dbOpenSnapshot)

    Do While Not rs.EOF
        Debug.Print rs!EmployeeID, rs!LastName, rs!FirstName
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

In the code, CurrentDb refers to the current Access database, and OpenRecordset returns records in the order specified by the SQL. EOF becomes true after the last record, and MoveNext advances through the result. The output goes to the Immediate window in the VBA editor, which you can show with Ctrl+G on Windows. This example only reads and prints values; replace the loop body with the work your procedure needs to perform.

If your procedure must edit records, choose an appropriate editable recordset type and confirm that the underlying query is updateable. A complex query, aggregate, or join may not allow edits even if the recordset is opened as a dynaset. Sorting alone does not make a query updateable.

Sort records displayed by a form

For a form, set its OrderBy property to a comma-separated list of fields and set OrderByOn to True. Put this in a form event procedure or call it from a button. The fields must be available in the form’s record source.

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

To stop applying the form’s ordering, set Me.OrderByOn = False. The form’s record source and its records remain in place; this turns off the form-level sort rather than rearranging rows in the underlying table. If the form should always open in a particular order, set its design-time Order By property, or apply the sort in an event that runs when the form opens. A button is useful when users should choose when to change the display.

For a form whose Record Source is already a saved query with an ORDER BY clause, that query may provide the initial order. Avoid maintaining conflicting sorts in both the query and form unless you have a reason to do so. Keeping the ordering rule in one clear location makes later changes easier to understand.

Sort by several fields or directions

Each field in an ORDER BY list can have its own direction. For instance, a task list might show the newest due date first, then order tasks with the same date by title alphabetically:

SELECT [TaskID], [DueDate], [Title]
FROM [Tasks]
ORDER BY [DueDate] DESC, [Title] ASC, [TaskID] ASC;

The first field has priority, the second breaks ties in the first, and the third makes remaining ties deterministic. This is sometimes called a compound or multi-key sort. It is useful for grouped-looking lists, but it does not group records in the database or change their values.

For ascending text sorts, Access uses the database’s sort order and text comparison rules. Do not assume that punctuation, accented characters, or case will sort exactly as they would in another database system or locale. If a particular order is important, test representative values in the actual database. You can also sort by an expression, but expressions should be clear and compatible with the query’s data types.

Saved queries, parameters, and dynamic sort choices

You can place the sort in a saved select query and open that query from VBA. This can keep SQL out of a long procedure and makes the query available to other Access objects. Use a QueryDef when the query needs parameters, such as a date range; supply those parameter values before opening its recordset. A parameter represents a value, not a field name or an entire ORDER BY expression.

If users can choose a sort field, do not insert arbitrary text from a textbox directly into SQL. SQL parameters cannot safely stand in for identifiers such as field names. Instead, map each permitted choice to a fixed, known sort expression, such as [LastName] ASC or [HireDate] DESC, then use only that approved expression. This avoids malformed SQL and prevents a user-entered string from changing the meaning of the statement. For ordinary data values used in criteria, use query parameters rather than concatenating untrusted input into SQL.

Common sorting problems

  • The records look unchanged: Check that the SQL includes ORDER BY, or that the form has OrderByOn = True. Setting the form’s OrderBy text alone does not enable it.
  • The order is inconsistent for ties: Add another sort field, ideally a unique primary key, at the end of the list.
  • Numbers appear in an unexpected order: Check whether the field is Number or Short Text. Text values are compared as text, not as quantities.
  • Access reports a syntax or parameter error: Verify field and table names, use brackets where appropriate, and check for misspellings. A name Access cannot resolve may be interpreted as a parameter prompt.
  • The form does not have the requested field: Confirm the field is included in the form’s Record Source. A control being visually hidden is different from the field being absent from the record source.
  • Editing fails after sorting: Check whether the record source is updateable and whether the recordset was opened read-only. A snapshot is not editable.

Null values deserve a deliberate check when users depend on their placement. Their position can be noticeable in a sorted list, and the desired treatment may differ from the default behavior. If blanks must always appear last, consider adding a calculated sort key that distinguishes missing values from populated ones, then sort by that key and the actual field. Confirm the expression against your Access version and data before using it in a production query.

Back up the database and run code safely

Before running a VBA procedure that changes records, save a separate copy of the Access database. VBA actions are not generally reversed by Ctrl+Z, so a backup is the reliable way to recover from an unintended change. The recordset example above is read-only and the form example changes the displayed order, but many real procedures combine sorting with updates or deletions. Review those operations separately before running them against important data.

Use a trusted database and enable macros only for files whose origin you trust. Avoid weakening Access’s global security settings simply to make one file run. Test changes on a copy with a small, representative set of records, and verify both the order and any effects on data. Sorting is usually low risk by itself, but code that processes the sorted rows can still have significant consequences.

Frequently Asked Questions

Can VBA permanently sort the rows in an Access table?

No. A table has no guaranteed display sequence for VBA to permanently rearrange. Store a sort key such as a date or sequence number if the order is meaningful, then use a query or form sort whenever that order should be shown.

Should I use SQL ORDER BY or a form’s OrderBy property?

Use SQL ORDER BY when code opens a sorted result or when the query itself should define the order. Use a form’s OrderBy and OrderByOn properties when you want to change the order users see in that form.

Can I sort a DAO recordset after opening it?

The most straightforward approach is to include ORDER BY in the SQL before opening the recordset. DAO also has sorting behavior for certain recordset types, but an explicit ordered query is easier to review and less likely to be confused with a table’s physical storage.

How do I sort newest records first?

Use a date or timestamp field followed by DESC, for example ORDER BY [CreatedAt] DESC. Add a unique key after the date if records with identical timestamps need a stable order.

Does sorting a form change the saved table or query?

No. Applying a form’s sort changes the order in which that form presents its records. It does not rewrite table rows or permanently alter the saved data.

Related

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.