ADO Filter Property
The Filter property sets or returns a variant value that contains a
filter for the data in a Recordset object. The filter allows you to select
records that fit a specific criteria. You also use this property to turn an
existing Filter off. Records that do not meet your criteria's are
said to be filtered out.
The Filter property can contain one of the following:
- A criteria string
- An array of bookmarks
- One of the FilterGroupEnum values
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, 'Beatrice'). Dates must be enclosed
in a pair of pound signs (for example, #12/25/2005#). Numbers can be
preceded by a dollar sign (for example, $99.95).
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.
Syntax
Examples of a criteria string:
-
objRecordset.Filter="FirstName='Beatrice'
OR FirstName='Betty'"
-
objRecordset.Filter="Lastname="LastName
LIKE Jo*"
-
objRecordset.Filter=="Birthdate
>= #11/15/1980#"
-
objRecordset.Filter="[Company
Name]='K?niglich Essen' OR Orders>$3000.00"
Example of an array of bookmarks:
Dim objRecordset AS New
ADODB.Recordset
Dim MyBookmarks(10)
MyBookmarks(2)=objRecordset.Bookmark
objRecordset.Filter=MyBookmarks(2)
When the Filter property is set, the cursor moves to the first record in
the filtered Recordset. And, when the Filter property is cleared, the cursor
moves to the first record in the unfiltered Recordset.
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 |
|