Ordering Records

Ordering is a way of defining how the data returned in the recordset is to be sorted. For example, you might want to see, in ascending order of amount, a list of customers who owe you money.

There are three ways to sort recordsets: using the WHERE clause in a query on which the recordset can be based, or in its Source argument; using the Index property; or using the Sort property. You can only use the Index property on table-type recordsets, whereas the WHERE clause and Sort property only work with dynaset- and snapshot-type recordsets.

Ordering Using the WHERE Clause

When you specify the SQL statement on which a recordset is based, you can terminate the query with an ORDER BY clause. This clause specifies three things: the columns on which the sort will be based, the order of precendence for the sorting of those columns, and the actual order in which the data in those columns will be sorted. For example:

SELECT * FROM tblCustomers ORDER BY CustomerNo DESC, CustName

In the above query, the records returned will be ordered according to the criteria set up for both the CustomerNo and CustName columns. By virtue of their relative positions in the clause (CustomerNo appears before CustName), the recordset will first be sorted according to the criteria for CustomerNo, and then by CustName. As you can see, CustomerNo will be sorted in descending order.

Although you can specify ASC, for ascending order, the default is ascending, so there is no need to explicitly declare it.

Ordering Using the Index Property

Setting the Index property of a table-type recordset is quite simple to execute; however, you are restricted to the sort order already specified by the table's index. For example:

rst.Index = "CustomerNo"

will immediately reorder the recordset in CustomerNo order. If the CustomerNo index is defined in ascending order, that is how the recordset will be sorted.

Ordering Using the Sort Property

As with the Filter property discussed above, setting the Sort property will not affect the current recordset. Rather, it will only affect a new recordset that is created and based on the current one.

For example, if we create a recordset, filtered on CustomerNo, we then set this recordset's Sort property, by specifying the WHERE clause of an SQL query, without the word WHERE. For example:

Set rst = dbs.OpenRecordset( _

"SELECT * FROM tblCustomers WHERE CustomerNo > 1234") rst.Sort = "[CustomerNo] DESC, [CustName]"

Then we create a new recordset whose sort order is defined by the Sort property, like this:

Set rsOrdered = rst.OpenRecordset

0 0

Post a comment