ADODB Object

Filter

 
Syntax
 
variant = recordsetobject.Filter
recordsetobject.Filter = variant
 
Description
Sets or returns a variant value that is either a string, array of bookmarks, or a FilterGroupEnum value used to filter data.You also use this property to turn an existing Filter off.
 
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, 'John Doe'). 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).
 
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
See Also
AbsolutePosition, PageSize, RecordCount, Sort, SQL.RunQuerySearch.
Example
 
rsRecordSet.Filter = " FirstName = ' Wilma' OR FirstName = 'Betty' "
 
 
rsRecordSet.Filter = " LastName LIKE F* "
 
 
rsRecordSet.Filter = " Date  >=  #12/25/2001# "
 
 
rsRecordSet.Filter = " Cost > $100.00 AND Cost < $200.00 "
 
 
Dim aMyBookmarks(5)
...
aMyBookmarks(3) = rsRecordSet.Bookmark
...
rsRecordSet.Filter = aMyBookmarks
 
Explanation:
 
 
 
 
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
 
 
objRecordset.Filter = ""
'Or
objRecordset.Filter = adFilterNone
 
Explanation:
 
 
 
 
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.