Using Recordset Filters

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
.

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:

FieldName must be a valid field name from the Recordset
. If the field name contains spaces, we must enclose the name in square brackets.
Operator must be one of the following: <
, >
, <=
, >=
, <>
, =
, LIKE
.
Value is the value with which we will compare the field's values (for example, '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.