Multiple Fields in Criteria Expressions [Access 2003 VBA Language Reference]

  • Article

You can specify multiple fields in a criteria argument.

To specify multiple fields in the criteria argument, you must ensure that multiple string expressions are concatenated correctly to form a valid SQL WHERE clause. In an SQL WHERE clause with multiple fields, fields may be joined with one of three keywords: AND, OR, or NOT. Your expression must evaluate to a string that includes one of these keywords.

For example, suppose that you wish to set the Filter property of an Employees form to display records restricted by two sets of criteria. The following example filters the form so that it displays only those employees whose title is "Sales Representative" and who were hired since January 1, 1993:

Dim datHireDate As Date
Dim strTitle As String

datHireDate = #1/1/93#
strTitle = "Sales Representative"

Forms!Employees.Filter = "[HireDate] >= #" & _
    datHireDate & "# AND [Title] = '" & strTitle & "'"
Forms!Employees.FilterOn = True

The criteria argument evaluates to the following string:

"[HireDate] >= #1-1-93# AND [Title] = 'Sales Representative'"

Tip

criteria

See Also | Date and Time Criteria Expressions | Date and Time Criteria from a Control on a Form | Domain Aggregate Functions | Numeric Criteria Expressions | Numeric Criteria from a Control on a Form | Restrict Data to a Subset of Records | Textual Criteria Expressions | Textual Criteria from a Control on a Form