Sort Property

Applies To

Dynaset-Type Recordset Object, Recordset Object, Snapshot-Type Recordset Object.

Description

Sets or returns the sort order for records in a Recordset object.

Settings and Return Values

The setting or return value is a string expression that contains the ORDER BY clause of an SQL statement without the reserved words ORDER BY. (Data type is String.)

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 following examples show how you can set the sort order to ascending or descending (Z to A or 100 to 0):

rstOrders.Sort = "ShipCountry"    ' Defaults to ascending.
rstOrders.Sort = "ShipCountry Asc"    ' Ascending.
rstOrders.Sort = "ShipCountry Desc"    ' Descending.

The Sort property doesn't apply to table-type Recordset objects. To sort a table-type Recordset object, use the Index property.

Tip

In many cases, it's faster to open a new Recordset object using an SQL statement that includes the sorting criteria.

See Also

Filter Property, Index Property, QueryDef Object, SQL Property.

Example

This example uses the Sort property to set the sort order of a dynaset-type Recordset based on an Orders table. The records in rstSorted will be in ascending Ship Country order.

Dim dbsNorthwind As Database
Dim rstOrders As Recordset, rstSorted As Recordset
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
Set rstOrders = dbsNorthwind.OpenRecordset("Orders", dbOpenDynaset)
rstOrders.Sort = "ShipCountry"    ' Set sort order.
Set rstSorted = rstOrders.OpenRecordset()    ' Create second dynaset.

Tip

In some situations, it may be more efficient to create the second Recordset object with the conditions you want in one step. As a general rule, 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.

Dim dbsNorthwind As Database, rstOrders As Recordset
Dim strSelect As String
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
strSelect = "SELECT * FROM Orders ORDER BY ShipCountry;"
Set rstOrders = dbsNorthwind.OpenRecordset(strSelect)

Example (Microsoft Access)

The following example uses the Sort property to set the sort order of a dynaset-type Recordset object based on an Orders table. The records in the rstSorted recordset variable will be ordered alphabetically by ship country.

Note that you first create a dynaset-type Recordset object and set its Sort property, then open a second dynaset-type Recordset object based on the first. Setting the Sort property of the first Recordset object doesn't actually affect the order of the records that it contains, so you need to create a second Recordset object in order to see the effects of the sort.

Sub SortByCountry()
    Dim dbs As Database
    Dim rstOrders As Recordset, rstSorted As Recordset

    ' Return Database variable that points to current database.
    Set dbs = CurrentDb
    ' Create dynaset-type Recordset object on Orders table.
    Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)
    ' Set sort order.
    rstOrders.Sort = "ShipCountry"
    ' Create second dynaset-type Recordset object.
    Set rstSorted = rstOrders.OpenRecordset()
End Sub

Tip

In most situations, it is more efficient to create the second Recordset object with the desired conditions in one step. As a general rule, when you know the data you want to select, it's usually more efficient to create a Recordset object with an SQL statement. The following example shows how you can create just one Recordset object and obtain the same results as in the preceding example.

Dim dbs As Database, rstOrders As Recordset
Dim strSelect As String
Set dbs = CurrentDb
strSelect = "SELECT * FROM Orders ORDER BY [ShipCountry];"
Set rstOrders = dbs.OpenRecordset(strSelect)

Example (Microsoft Excel)

This example creates a new recordset from the Supplier recordset in the NWINDEX.MDB database, and then it copies the new recordset onto Sheet1. The new recordset is sorted on the COUNTRY field, in ascending order.

To create the NWINDEX.MDB database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs As Recordset, sortedSet As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("Supplier", dbOpenDynaset)
rs.Sort = "[COUNTRY]"
Set sortedSet = rs.OpenRecordset()
Sheets("Sheet1").Activate
ActiveCell.CopyFromRecordset sortedSet
sortedSet.Close
rs.Close
db.Close