Recordset.Sort property (DAO)
Applies to: Access 2013, Office 2013
Sets or returns the sort order for records in a Recordset object (Microsoft Access workspaces only).
Syntax
expression .Sort
expression A variable that represents a Recordset object.
Remarks
You can use the Sort property with dynaset– and snapshot–type Recordset objects.
When you set this property for an object, sorting occurs when a subsequent Recordset object is created from that object. The Sort property setting overrides any sort order specified for a QueryDef object.
The default sort order is ascending (A to Z or 0 to 100).
The Sort property doesn't apply to table– or forward–only–type Recordset objects. To sort a table–type Recordset object, use the Index property.
Note
In many cases, it's faster to open a new Recordset object by using an SQL statement that includes the sorting criteria.
Example
This example demonstrates the Sort property by changing its value and creating a new Recordset. The SortOutput function is required for this procedure to run.
Sub SortX()
Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim rstSortEmployees As Recordset
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees", _
dbOpenDynaset)
With rstEmployees
SortOutput "Original Recordset:", rstEmployees
.Sort = "LastName, FirstName"
' Print report showing Sort property and record order.
SortOutput _
"Recordset after changing Sort property:", _
rstEmployees
' Open new Recordset from current one.
Set rstSortEmployees = .OpenRecordset
' Print report showing Sort property and record order.
SortOutput "New Recordset:", rstSortEmployees
rstSortEmployees.Close
.Close
End With
dbsNorthwind.Close
End Sub
Function SortOutput(strTemp As String, _
rstTemp As Recordset)
With rstTemp
Debug.Print strTemp
Debug.Print " Sort = " & _
IIf(.Sort <> "", .Sort, "[Empty]")
.MoveFirst
' Enumerate Recordset.
Do While Not .EOF
Debug.Print " " & !LastName & _
", " & !FirstName
.MoveNext
Loop
End With
End Function
When you know the data you want to select, it's usually more efficient to create a Recordset with an SQL statement. This example shows how you can create just one Recordset and obtain the same results as in the preceding example.
Sub SortX2()
Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
' Open a Recordset from an SQL statement that specifies a
' sort order.
Set rstEmployees = _
dbsNorthwind.OpenRecordset("SELECT * " & _
"FROM Employees ORDER BY LastName, FirstName", _
dbOpenDynaset)
dbsNorthwind.Close
End Sub