Filter Property (ADO)
The Filter property on a Recordset object indicates a filter for data in a Recordset. This property sets or returns a Variant value.
recordset.Filter = Criteria
- This parameter specifies a Variant.
It can be either a criteria (a where clause) or one of the enumerated values for FilterGroupEnum listed in the following table.
Enumeration Value Description adFilterNone 0 No filter. This value removes the current filter and restores all records to view. adFilterPendingRecords 1 Use the pending records. This value allows viewing only those records that have changed but have not yet been sent to the server. This value is only applicable for batch update mode. adFilterAffectedRecords 2 Use only records affected by the last Delete, Resync, UpdateBatch, or CancelBatch call. adFilterFetchedRecords 3 Use the last fetched records. This value allows viewing the records in the current cache returned as a result of the last call to retrieve records (implying a resynchronization). adFilterConflictingRecords 5 Use the conflicting records. This value allows viewing only those records that failed the last batch update.
The Filter property is not supported by the Microsoft® OLE DB Provider for DB2 or the Microsoft ODBC Driver for DB2.
The Filter property is supported by the Microsoft OLE DB Provider for AS/400 and VSAM on certain files. In order to use the Recordset Filter property, an AS/400 logical file, an AS/400 keyed physical file, a mainframe KSDS file with a unique key, or a mainframe RRDS file with a unique key must be used. If this property is used on an AS/400 non-keyed physical file or any other mainframe file type, then the method fails.
When the Filter property is used with a criteria, the where clause is a combination of triplets. Each triplet consists of a column name, an operator, and a literal value. These where clause triplets can be combined with ANDs and ORs for more complex logical filters.
If Criteria is a single-condition where clause, then any operator can be used. The construction of a single-condition where clause consists of a column name (the database field), an operator (greater than or equal, for example), and a literal value.
Examples of a single-condition where clause is as follows:
- recordset.Filter = "LastName = 'Jones' "
- recordset.Filter ="Salary > 30000.0"
The Criteria argument can be a two-condition with the following restrictions:
- If the column name (the database field) is the same in both clauses, then the separate where clauses must define a contiguous range.
- If the column names are different, then the operators must be the same. If the operators are "LIKE", then the filtered region may be unexpected.
Examples of acceptable two-condition where clauses are as follows:
- recordset.Filter = "LastName = 'Jones' AND FirstName = 'Tom' "
- recordset.Filter = "Cost = 5000.00 OR Cost > 5000.00 "
The Criteria argument can be three or more conditions with the following restrictions:
- The operators must be the same for all conditions. If the operators are "LIKE", then the filtered region may be unexpected.
In all cases, if the "=" operator is used, then the column names specified in the where clause must be keyed columns in the file.
One restriction on these combinations is that OR clauses can only be used at the highest (major) level of the logical operation.
Examples of acceptable Criteria meeting these conditions are:
- recordset.Filter = "(Title='Manager' AND Salary>30000) OR (Title='Administrator' AND Salary>50000)"
- recordset.Filter ="Salary > 30000.0"
An example of illegal Criteria is:
- recordset.Filter = "(Title='Associate' OR Salary >30000) AND (Title='Administrator')"
The operator can also use wildcards (* or %) in character expressions as follows:
- recordset.Filter = "Lastname LIKE '*SMITH*'"
To determine if any records were found meeting the Criteria, the application should check the Recordset EOF property. If EOF is true, then no records were found meeting the where clause specified in the Criteria parameter.
If the CursorLocation Property is set to adUseClient (use the client cursor engine), the Filter property will work if MDAC 2.0 or later is installed but will not work properly with earlier versions of ADO.
When operating on large VSAM files and only querying data on a subset of the records, using the Filter property is not desirable because of the performance impact. The entire VSAM file is transferred to the client for filtering. A better solution is to use the server cursor engine and the Find method.To download updated Host Integration Server 2004 Help from www.microsoft.com, go to http://go.microsoft.com/fwlink/?linkid=29507. Copyright © 2004 Microsoft Corporation.
All rights reserved.