Filter Property

Applies To Dynaset-Type Recordset object, Forward-Only – Type Recordset object, Recordset object, Snapshot-Type Recordset object.

Description

Sets or returns a value that determines the records included in a subsequently opened Recordset object (Microsoft Jet workspaces only).

Settings and Return Values

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

Remarks

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.

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

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 Jet 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.

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 Jet SQL only accepts U.S. decimal characters.

See Also OpenRecordset method, Sort property, WHERE clause.

Specifics (Microsoft Access)

The DAO Filter property applies a filter to a Recordset object. You can set it only by using Visual Basic.

Note The Microsoft Access Filter property applies a filter to a form. You can set it by using a macro, Visual Basic, or a form's property sheet in form Design view.

Example

This example uses the Filter property to create a new Recordset from an existing Recordset based on a specified condition. The FilterField function is required for this procedure to run.

Sub FilterX()

    Dim dbsNorthwind As Database
    Dim rstOrders As Recordset
    Dim intOrders As Integer
    Dim strCountry As String
    Dim rstOrdersCountry As Recordset
    Dim strMessage As String

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstOrders = dbsNorthwind.OpenRecordset("Orders", _
        dbOpenSnapshot)

    ' Populate the Recordset.
    rstOrders.MoveLast
    intOrders = rstOrders.RecordCount

    ' Get user input.
    strCountry = Trim(InputBox( _
        "Enter a country to filter on:"))

    If strCountry <> "" Then
        ' Open a filtered Recordset object.
        Set rstOrdersCountry = _
            FilterField(rstOrders, "ShipCountry", strCountry)

        With rstOrdersCountry
            ' Check RecordCount before populating Recordset;
            ' otherwise, error may result.
            If .RecordCount <> 0 Then .MoveLast
            ' Print number of records for the original
            ' Recordset object and the filtered Recordset
            ' object.
            strMessage = "Orders in original recordset: " & _
                vbCr & intOrders & vbCr & _
                "Orders in filtered recordset (Country = '" & _
                strCountry & "'): " & vbCr & .RecordCount
            MsgBox strMessage
            .Close
        End With

    End If

    rstOrders.Close

    dbsNorthwind.Close

End Sub

Function FilterField(rstTemp As Recordset, _
    strField As String, strFilter As String) As Recordset

    ' Set a filter on the specified Recordset object and then
    ' open a new Recordset object.
    rstTemp.Filter = strField & " = '" & strFilter & "'"
    Set FilterField = rstTemp.OpenRecordset

End Function

Note To see the effects of filtering rstOrders, you must set its Filter property, and then open a second Recordset object based on rstOrders.

Note When you know the data you want to select, it's usually more efficient to create a Recordset with an SQL statement. This example shows how you can create just one Recordset and obtain records from a particular country.

Sub FilterX2()

    Dim dbsNorthwind As Database
    Dim rstOrders As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Open a Recordset object that selects records from a
    ' table based on the shipping country.
    Set rstOrders = _
        dbsNorthwind.OpenRecordset("SELECT * " & _
        "FROM Orders WHERE ShipCountry = 'USA'", _
        dbOpenSnapshot)

    rstOrders.Close
    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example prompts the user to enter the name of a country on which to filter, and then sets the Filter property of a dynaset-type Recordset object. For example, if the user enters "Italy," the filtered recordset will contain only those records in which the ShipCountry field contains the string "Italy."

Note that you first create a dynaset-type Recordset object and set its Filter property, then open a second dynaset-type Recordset object based on the first. Setting the Filter property of the first Recordset object doesn't actually affect the records that it contains, so you need to create a second Recordset object in order to see the effects of the filter.

Sub SetRecordsetFilter()
    Dim dbs As Database, strInput As String
    Dim rstOrders As Recordset, rstFiltered As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Prompt for value on which to filter.
    strInput = InputBox("Enter name of country on which to filter.")
    ' Create dynaset-type Recordset object.
    Set rstOrders = dbs.OpenRecordset("Orders", dbOpenDynaset)
    ' Set filter condition.
    rstOrders.Filter = "ShipCountry = '" & strInput & "'"
    ' Create filtered dynaset-type Recordset object.
    Set rstFiltered = rstOrders.OpenRecordset
    rstOrders.Close
    rstFiltered.Close
    Set dbs = Nothing
End Sub

Tip In most situations, it's more efficient to create the second Recordset object with the desired conditions in one step. When you know what data you want to select, it's generally more efficient to create a recordset with an SQL statement. The next example shows how you can create just one recordset and obtain the same results as in the preceding example:

Sub CreateRecordsetWithSQL()
    Dim dbs As Database, rst As Recordset
    Dim strInput As String

    Set dbs = CurrentDb
    strInput = InputBox("Enter name of country on which to filter.")
    Set rst = dbs.OpenRecordset("SELECT * FROM Orders " _
        & "WHERE ShipCountry = '" & strInput & "';")
    rst.MoveLast
    MsgBox "Recordset contains " & rst.RecordCount & " records."
    rst.Close
    Set dbs = Nothing
End Sub

Example (Microsoft Excel)

This example creates a new recordset that contains records from the Supplier recordset in the Nwindex.mdb database, and then it copies the recordset contents to Sheet1. These records contain only data on suppliers located in Canada. The example copies a new, sorted recordset to Microsoft Excel.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim db As Database, rs As Recordset, sortedSet As Recordset
Set db = Workspaces(0).OpenDatabase(Application.Path & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("Supplier", dbOpenDynaset)
rs.Filter = "[COUNTRY] = 'Canada'"
Set sortedSet = rs.OpenRecordset()
Sheets("Sheet1").Activate
ActiveCell.CopyFromRecordset sortedSet
sortedSet.Close
rs.Close
db.Close