Recordset.Filter Property (DAO)

Sets or returns a value that determines the records included in a subsequently opened Recordset object (Microsoft Access workspaces only). Read/write String.

Syntax

expression .Filter

expression An expression that returns a Recordset object.

Remarks

The setting or return value is a String data type that contains the WHERE clause of an SQL statement without the reserved word WHERE.

Use the Filter property to apply a filter to a dynaset–, snapshot–, or forward–only–type Recordset object.

You can use the Filter property to restrict the records returned from an existing object when a new Recordset object is opened based on an existing Recordset object.

Use the U.S. date format (month-day-year) when you filter fields containing dates, even if you're not using the U.S. version of the Microsoft Access database engine (in which case you must assemble any dates by concatenating strings, for example, strMonth & "-" & strDay & "-" & strYear). Otherwise, the data may not be filtered as you expect.

In many cases, it's faster to open a new Recordset object by using an SQL statement that includes a WHERE clause.

If you set the property to a string concatenated with a non–integer value, and the system parameters specify a non-U.S. decimal character such as a comma (for example, strFilter = "PRICE > " & lngPrice, and lngPrice = 125,50), an error occurs when you try to open the next Recordset. This is because during concatenation, the number will be converted to a string using your system's default decimal character, and Microsoft Access SQL only accepts U.S. decimal characters.

Example

The following sample shows how to use the Filter property to determine the records to be included in a subsequently opened Recordset.

Sample code provided by: The Microsoft Access 2010 Programmer’s Reference | About the Contributors

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstFiltered As DAO.Recordset
Dim strCity As String

Set dbs = CurrentDb

'Create the first filtered Recordset, returning customer records
'for those visited between 30-60 days ago.
Set rest = dbs.OpenRecordset(_ 
    "SELECT * FROM Customers WHERE LastVisitDate BETWEEN Date()-60 " & _
    "AND Date()-30 ORDER BY LastVisitDate DESC")

'Begin row processing
Do While Not rst.EOF
    
    'Retrieve the name of the first city in the selected rows
    strCity = rst!City

    'Now filter the Recordset to return only the customers from that city
    rst.Filter = "City = '" & strCity & "'"
    Set rstFiltered = rst.OpenRecordset

    'Process the rows
    Do While Not rstFiltered.EOF
        rstFiltered.Edit
        rstFiltered!ToBeVisited = True
        rstFiltered.Update
        rstFiltered.MoveNext
    Loop

    'We've done what was needed. Now exit
    Exit Do
    rst.MoveNext
   
Loop

'Cleanup
rstFiltered.Close
rst.Close

Set rstFiltered = Nothing
Set rst = Nothing

About the Contributors

Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.