ADO - Filter

In ADO (ActiveX Data Objects), the Filter property is used to limit (filter) the records displayed in a Recordset based on specific criteria — similar to using a WHERE clause in SQL, but done after the data has already been retrieved.

1. Purpose

Filter allows you to show only certain records from an existing Recordset that meet a condition, without running a new SQL query.

2. Syntax

rs.Filter = criteria

or

rs.Filter = adFilterNone     ' Removes the filter

3. Types of Criteria

The criteria can be:

  • A string (similar to an SQL WHERE clause)

  • A comparison constant

  • A bookmark array (to display specific records)

4. Examples

Example 1: Filter by a Field Value

rs.Open "SELECT * FROM Employees", conn, adOpenStatic, adLockReadOnly

' Show only employees in the Sales department
rs.Filter = "Department = 'Sales'"

Do Until rs.EOF
    MsgBox rs("EmployeeName")
    rs.MoveNext
Loop

Example 2: Filter by Numeric Comparison

' Show employees with salary greater than 50000
rs.Filter = "Salary > 50000"

Example 3: Filter by Multiple Conditions

' Show employees in Sales department earning over 50000
rs.Filter = "Department = 'Sales' AND Salary > 50000"

Example 4: Remove the Filter

To return to all records:

rs.Filter = adFilterNone

5. Constants You Can Use

ADO defines a few constants for the Filter property:

Constant Description
adFilterNone Removes any filter (shows all records).
adFilterPendingRecords Shows records that haven’t been updated yet.
adFilterAffectedRecords Shows records affected by the last batch update.
adFilterFetchedRecords Shows only records that have been retrieved from the data source.

6. Notes

  • The filter creates a new view of the Recordset; it doesn’t modify the original data.

  • You can assign the filtered Recordset to another Recordset:

    Set rsFiltered = rs
rsFiltered.Filter = "City = 'London'"

  • Works only if the Recordset supports client-side cursors (for example, adUseClient).

7. Example Summary

rs.Open "SELECT * FROM Employees", conn, adOpenStatic, adLockOptimistic
MsgBox "All records: " & rs.RecordCount

rs.Filter = "Department = 'HR'"
MsgBox "Filtered records: " & rs.RecordCount

rs.Filter = adFilterNone   ' Removes the filter