Filtering and Ordering Recordsets

Whenever you work on records in a database, it is rare that you want to carry out an action on the entire table. If you did, you would be best served by using an action query because queries operate much faster on large numbers of rows than do row processing methods (recordsets). However, it is more likely that you'll want to do something with a subset of records, and that means you would need to filter your query to select only those records that you wanted to work on.

With recordsets, you have the additional opportunity to sort the records, so you can operate on them in a specific order, perhaps by ascending date, for example. This section illustrates how to filter your recordsets and order their output.

Filtering Records

Filtering is simply a way of restricting the number of rows returned by a recordset so that you can minimize the amount of data you have to wade through. The additional benefit of filtering is that it also reduces the amount of data that is sent across the network, thereby minimizing bandwidth usage. As you've already seen, you can filter a recordset using a WHERE clause in a query on which the recordset can be based, or in its Source argument. For example:

Set rst = dbs.OpenRecordset( _
    "SELECT * FROM tblCustomers WHERE CustomerNo > 1234")

This filters the recordset as it is being created. Of course, you can't do this on table-type recordsets because they load the entire table. You can, however, filter dynaset- and snapshot-type recordsets.

Another method of filtering a recordset as it is being created is to use the Recordset object's Filter property. You can't filter an existing recordset once it's been created, so the filter won't take effect until you create a new recordset that is based on the first.

For example, if you create a recordset such as the previous one (filtered on CustomerNo), you can then further filter its records and place the output into a second recordset. You do this by setting its Filter property, by specifying the WHERE clause of an SQL query, without the word WHERE. For example:

rst.Filter = "[CustName] LIKE '*parts*"

Once the Filter property has been set, you can create a new recordset that will be based on a subset of the rows in the first recordset such as this:

Set rstFiltered = rst.OpenRecordset

After doing so, rstFiltered contains only those rows from rst whose CustName rows contains the word parts. You might think that this is a rather inefficient way of doing things, and under normal circumstances you'd be right; however, there are circumstances in which this approach might be the better way to go.

For example, say you want your sales representatives to visit all the customers in a certain city, based solely on when that city that was last visited. You don't know which city that might be, so the following example code creates a recordset that returns rows for all customers who were last visited between 30 and 60 days ago. Once you have the record for the last customer visited within that time frame, you then extract the name of the city in which they reside, and create another filtered recordset (based on the first), and set their ToBeVisited flag to True. This lets the sales represtentatives know to visit them. Of course, there's nothing here that couldn't be done in an action query, but this example demonstrates how you could use this feature.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstFiltered As DAO.Recordset
Dim strCity As String

Set dbs = CurrentDb
'Create the first filtered recordset, returning customer records
'for those visited between 30-60 days ago.

Set rst = dbs.OpenRecordset( _
"SELECT * FROM Customers WHERE LastVisitDate BETWEEN Date()-60 " & _
"AND Date()-30 ORDER BY LastVisitDate DESC")

'Begin row processing
Do While Not rst.EOF

    'Retrieve the name of the first city in the selected rows
    strCity = rst!City
    'Now filter the recordset to return only the customers from that city
    rst.Filter = "City = '" & strCity & "'"
    Set rstFiltered = rst.OpenRecordset

    'Process the rows
    Do While Not rstFiltered.EOF
	rstFiltered.Edit
	rstfiltered!ToBeVisited = True
	rstFiltered.Update
	rstFiltered.MoveNext
    Loop

    'We've done what hat needed. Now exit.
    Exit Do
    rst.MoveNext
Loop

'Cleanup
rstFiltered.Close
rst.Close

Set rstFiltered = Nothing
Set rst = Nothing

Notice the ORDER BY clause in this example? It's explained in the next section.

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 ORDER BY 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 ORDER BY clause and Sort property work only with dynaset- and snapshot-type recordsets.

Ordering Using the ORDER BY 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 this 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.

The default order is ascending, so although you can specify ASC, there's no need to explicitly declare it.

Ordering Using the Index Property

Setting the Index property of a table-type recordset is quite simple; however, you are restricted to the sort order already specified by the table's index. For example, the following code 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.

rst.Index = "CustomerNo"

Ordering Using the Sort Property

As with the Filter property discussed previously, setting the Sort property does not affect the current recordset. Rather, it affects only a new recordset that is based on the current one.

For instance, if you create a recordset, filtered on CustomerNo, you set the recordset's Sort property by specifying the ORDER BY clause of an SQL query, without the words ORDER BY. For example:

Set rst = dbs.OpenRecordset( _
    "SELECT * FROM tblCustomers WHERE CustomerNo > 1234")
rst.Sort = "[CustomerNo] DESC, [CustName]"

Then you create a new recordset whose sort order is defined by the Sort property, such as this:

Set rstOrdered = rst.OpenRecordset