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 hasOrderByOn = 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
- Power Query Circular Reference: Causes and Fixes
- Excel Data Validation Source: Lists, Ranges, and Rules
- Excel Data Validation Rules: How They Work and When to Use Them
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Range by Column: Practical Examples and TipsTo sort a range by one column in Excel VBA, call the range’s Sort method and specify the…
- Range vs Cell in VBA: Differences, Syntax, and ExamplesIn Excel VBA, a cell is represented by a Range object: use Range to refer to an address…
- Excel VBA Progress Bar: How to Build a Responsive OneAn Excel VBA progress bar shows how much of a long-running macro has finished. For a visual bar,…
- Excel VBA Find Method: Syntax, Examples, and TipsIn Excel VBA, use the Range.Find method to search a range and return the first matching cell; if…
- Excel VBA Array Filter: Methods, Code, and ExamplesTo filter a one-dimensional array of strings in Excel VBA, use the built-in Filter function, for example Filter(names,…
- PowerPoint VBA: How to Loop Through SlidesTo loop through every slide in the open PowerPoint presentation, use a VBA For Each loop over ActivePresentation.Slides.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.