DevGuru ADO Recordset::Filter Property

PROPERTY: Recordset::Filter

variant = recordsetobject.Filter
recordsetobject.Filter = variant

The Filter property sets or returns a variant value that can be a Criteria String, an array of bookmarks, or one of the FilterGroupEnum constants. The purpose of a filter is to allow you to select records that fit specific criteria that you have specified. Records that do not meet your criteria's are said to be filtered out.

The Criteria String is composed of one or more clauses, where each clause has a FieldName, Operator, and a Value, in that order. Two or more clauses can be concatenated to each other using the AND or OR operators.

The FieldName is the valid name of a field in a Recordset. If it contains any blank spaces, it must be enclosed inside a pair of square brackets (for example, [Last Name]).
The Operator can only be one of the following:
= < > <= >= <> LIKE
If you use the LIKE operator, you can also use the * or % wildcards as the last character in the string or as the first and last character in the string.
The Value is the value that you want compared to the value in the field in the Recordset. It cannot be Null. Strings must be enclosed in a pair of single quotes (for example, 'DevGuru'). Dates must be enclosed in a pair of pound signs (for example, #12/25/2001#). Numbers can be preceded by a dollar sign (for example, $99.95).

Code (VBScript):
rsRecordSet.Filter = " FirstName = ' Wilma' OR FirstName = 'Betty' "
or
rsRecordSet.Filter = " LastName LIKE F* "
or
rsRecordSet.Filter = " Date >= #12/25/2001# "
or
rsRecordSet.Filter = " Cost > $100.00 AND Cost < $200.00 "

The Filter property can also be set equal to an array of bookmarks. Only those records that match one of the bookmarks will be returned.

Code (VBScript):
Dim aMyBookmarks(5)
...
aMyBookmarks(3) = rsRecordSet.Bookmark
...
rsRecordSet.Filter = aMyBookmarks

Also, the Filter property can set or return one of the FilterGroupEnum constants. A convenient way to determine if a filter is in effect is to test for adFilterNone.

FilterGroupEnum Constants

Constant	Value	Description
adFilterAffectedRecords	2	This filter only displays records changed by the last call to CancelBatch, Delete, Resync, or Update
adFilterConflictingRecords	5	This filter displays only those records that failed the last batch update
adFilterFetchedRecords	3	This filter displays the records in the current cache
adFilterNone	0	Removes the current filter and all underlying records become visible.
adFilterPendingRecords	1	This filter displays changed records that have not been saved

When the filter has been applied, the cursor will be placed at the first record in the Recordset. You can cancel a filter by setting it equal to "" or to adFilterNone.

Code (VBScript):
objRecordset.Filter = ""
or
objRecordset.Filter = adFilterNone