At the risk of sounding repetitive, a Recordset object works with a result set of data. We can think of a result set as a table that is a subset of the original table from which the Recordset retrieved its data. Consider the need to retrieve further subsets of this result data. We have two options—either create a new Recordset with the additional filtering criteria, or apply the filtering criteria to the existing record set.
Creating a new Recordset introduces a great deal of overhead and processing time, as well as the need to cache data that may be duplicated. To avoid this the Recordset object supplies a filtering method: Filter.
A Filter can be a string that provides filtering information to the Recordset, or an array of bookmarks on specific records in the record set. Keep in mind however, that the ADO must do the filtering work itself, and incur the processing overhead it entails. With a large recordset, this overhead can exceed the effort of simply creating a new recordset.
Let's take a look at how a string of filtering criteria might be applied. A criteria string is made up of values in the form FieldName Operator Value (for example, "LastName = 'Smith'"). We can create compound clauses by joining individual clauses with AND (for example, "LastName = 'Smith' AND FirstName = 'John'"). The following lists some guidelines for creating filter strings:
Recordset. If the field name contains spaces, we must enclose the name in square brackets.<, >, <=, >=, <>, =, LIKE.'Smith', #8/24/95#, 12.345 or $50.00). Use single quotes with strings and hash signs (#) with dates. For numbers, we can use decimal points, dollar signs, and scientific notation. If Operator is LIKE, Value can include wildcards. Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last character in the string. Value may not be Null.The filter property also allows us to supply a number of constants that effect the way in which the filter is applied to the data, and the results it produces. The constants that can be applied are:
| Constant | Value | Description |
adFilterNone |
0 |
Removes the current filter and restores all records to view. |
adFilterPendingRecords |
1 |
Allows you to view only records that have changed but have not yet been sent to the server. Only applicable for the batch update mode. |
adFilterAffectedRecords |
2 |
Allows you to view only records affected by the last Delete, Resync, UpdateBatch, or CancelBatch call. |
adFilterFetchedRecords |
3 |
Allows you to view records in the current cache, that is, the results of the last fetch from the database. |