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 (Microsoft Jet workspaces only).

Settings and Return Values

The setting or return value is a String that contains the ORDER BY clause of an SQL statement without the reserved words ORDER BY.

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.

See Also Filter property, Index property, ORDER BY clause ("SQL Language Reference" in Volume 1), QueryDef object, SQL property.

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

Note 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

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 reference to current database.
    Set dbs = CurrentDb
    ' Create dynaset-type Recordset object.
    Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)
    ' Set sort order.
    rstOrders.Sort = "ShipCountry"
    ' Create second dynaset-type Recordset object.
    Set rstSorted = rstOrders.OpenRecordset()
    ' Perform operations with recordset.
        .
        .
        .
    rstOrders.Close
    rstSorted.Close
    Set dbs = Nothing
End Sub

Tip In most situations, it's more efficient to create the second Recordset object with the desired conditions in one step. When you know the data you want to select, it's generally more efficient to create a Recordset object with an SQL statement. The next example shows how you can create just one Recordset object and obtain the same results as in the preceding example:

Sub CreateRecordsetWithSQL()

Dim dbs As Database, rst As Recordset

Dim strSelect As String

Set dbs = CurrentDb

strSelect = "SELECT * FROM Orders ORDER BY ShipCountry;"

Set rst = dbs.OpenRecordset(strSelect)

rst.MoveLast

MsgBox "Recordset contains " & rst.RecordCount & " records."

rst.Close

End Sub

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 to 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