Filter Property

Applies To

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

Settings and Return Values

A string expression that contains the WHERE clause of an SQL statement without the reserved word WHERE. The data type is String.

Remarks

Use the Filter property to apply a filter to a dynaset- or Snapshot-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.

The following example shows how the Filter property can be used to return only the records for customers who live in a particular region:

rstCustomers.Filter = "Region = 'NY'"
Set rstNYCustomers = rstCustomers.OpenRecordset()

Notes

In many cases, it's faster to open a new Recordset object 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; otherwise, the data may not be filtered as you expect. You can use the Visual Basic Format function to make this easier. For example:
```
rstEmployees.Filter = "HireDate > #" & Format(mydate, "m/d/yy") & "#"
```

See Also

OpenRecordset Method, Sort Property.

Specifics (Microsoft Access)

The data access Filter property applies a filter to a Recordset object. You can set it only from Visual Basic.

Note

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

Example

This example creates a dynaset-type Recordset that contains only records for orders shipped to the United Kingdom.

Dim dbsNorthwind As Database
Dim rstOrders As Recordset, rstFiltered As Recordset
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
' Create dynaset-type Recordset.
Set rstOrders = dbsNorthwind.OpenRecordset("Orders", dbOpenDynaset)
rstOrders.Filter = "ShipCountry = 'UK'"        ' Set filter condition.
' Create filtered dynaset.
Set rstFiltered = rstOrders.OpenRecordset()

Note

To change the contents of rstOrders, you must set its Filter property, and then open a second Recordset object based on rstOrders.

Tip

In some situations, it may be more efficient to create the second Recordset object with the conditions you want in one step. As a general rule, 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 the same results as in the preceding example:

Dim dbsNorthwind As Database, rstOrders As Recordset
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
Set rstOrders = dbsNorthwind.OpenRecordset("SELECT * FROM Orders " & _
    "WHERE ShipCountry = 'UK';")

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 value "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 Database variable that points 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()
    ' Populate recordset.
    rstFiltered.MoveLast
    ' Second Recordset object is now subset of the first.
    Debug.Print rstFiltered.RecordCount
End Sub

Tip

In most situations, it is 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 following example shows how you can create just one recordset and obtain the same results as in the preceding example.

Dim dbs As Database, rstOrders As Recordset, strInput As String
Set dbs = CurrentDb
strInput = InputBox("Enter name of country on which to filter.")
Set rstOrders = dbs.OpenRecordset("SELECT * FROM Orders " & _
    "WHERE [ShipCountry] = '" & strInput & "';")

Example (Microsoft Excel)

This example creates a new recordset containing records of the Supplier recordset in the NWINDEX.MDB database, and then it copies the recordset contents onto Sheet1. These records contain only data on suppliers located in Canada. The example copies a new, sorted recordset into 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