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, and of course, you can't do this on table-type recordsets, because they load the entire table, but you can 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 we create a recordset like that shown above (filtered on CustomerNo), we can then further filter its records and place the output into a second recordset. We do this by setting its Filter property, by specifying the WHERE clause of an SQL query, without the word WHERE. For example:

Then we create a new recordset that will be based on a subset of the rows in the first recordset that is defined by the Filter property, like this:

Set rsFiltered = rst.OpenRecordset

After doing so, rsFiltered 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 I'd agree with you; however, there are circumstances in which this approach might be the better way to go.

For example, let's say you want your sales representatives to visit all the customers in a certain city, based solely on the city that was visited last. You don't know which city that might be, so in the following example code, we create a recordset that returns rows for all my customers who were last visited between 30 and 60 days ago. Once we have the record for the last customer visited within that time frame, we 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 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


'for those visited between 30-60 days ago.

Set rst = dbs.OpenRecordset( _

"SELECT * FROM Customers WHERE LastVisitDate BETWEEN


AND Date()-3 0 ORDER BY LastVisitDate Desc")

'Begin row processing

Do While Not rst.EOF

'Retrieve the name of the first city in the selected


strCity = rst.City

'Now filter the recordset to return only the customers from that city

rst.Filter = "City = '" & strCity &

Set rsFiltered = rst

'Process the rows

Do While Not rsFiltered.EOF


rsfiltered!ToBeVisited = True




'We've done what hat needed. Now exit.

Exit Do

End If



You may have noticed that I used the ORDER BY clause in the preceding example. The ORDER BY clause is explained in the next section.

0 0

Post a comment