Filtering Recordsets

Searching for specific data in a Recordset object is easily accomplished using the Filter property. You can submit a new query or even use the Find method (which is discussed in the next chapter), but Filter is truer to the purpose of a criterion-based search. Submitting a new query completely changes the results of the Recordset and Find locates appropriate data one record at a time. The Filter property/method temporarily limits access to only those records that meet the filtering criteria. In other words, you end up with a subset of records from your Recordset.

When temporarily restricting the available records in a Recordset, use the Filter property in the form

 recordset.Filter = condition 

where recordset represents a Recordset object and condition is a criteria string. For instance, the following statement locates all the tasks for employee 1 (Larry Schaff):

 rstTasks.Filter = 1 

If the Recordset contained only the employee name fields, you might use one of the following statements:

 rstTasks.Filter = "LastName = 'Schaff'" 

or even

 rstTasks.Filter = "LastName = 'Schaff' AND FirstName = 'Larry'" 

When using the Filter property, the filtered Recordset becomes the current cursor. That means most of the Recordset properties and methods affect only the filtered results, not the original Recordset.

The condition expression can specify any field that's in the Recordset and use the following operators: <, >, <=, >=, <>, =, or LIKE. Remember to delimit the value component appropriately using single quotation marks for strings and the pound sign for dates. (Values don't need delimiters.)

To clear a filter, use the following syntax

 recordset.Filter = adFilterNone 

The adFilterNone constant is similar to setting the property to a zero-length string. The result is that the current record position moves to the first record in the original Recordset.

CAUTION

Access and VBA support the * and ? wildcard characters, but ADO doesn't. If you use a SQL statement or fixed query to populate an ADO Recordset, you must use ADO's wildcards. The * ADO equivalent is the percent character, %; the ADO ? equivalent is the underscore character, _. Using the * and ? characters won't generate an error. Instead, ADO interprets the characters literally and returns an empty Recordset or perhaps even erroneous data.