Filter Property

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
 

Parameters

Criteria

This parameter specifies a Variant and can be either a criteria (a where clause) or one of the following enumerated values for FilterGroupEnum:

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).
adFilterPredicate	4

Remarks

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*'"

In order to use the Recordset Filter property, an AS/400 logical file or a mainframe KSDS or RRDS file must be used. If this property is used on an AS/400 physical file or any other mainframe file type, then this property fails.

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.

Under some circumstances, the Recordset Filter property will not work properly. These situations can occur if the Recordset Filter is set and then the resulting data is examined immediately. The Recordset still contains all of the original records. Hence, RecordCount property returns the total number of records in the Recordset. This problem does not occur if other statements such as a MsgBox are executed immediately before setting the Filter property.

If the CursorLocation property is set to adUseClient (use the client cursor engine), the Filter property will work if MDAC 2.0 is installed but will not work properly with earlier versions of ADO.