Restrict Data to a Subset of Records
When working with records you will often need to restrict your data to a specific set of records. Some procedures take a criteria argument that enables you to specify what data should be returned. For example, you specify the criteria argument to restrict which records are returned when you use domain aggregate functions. You may also specify criteria when you use the Find method of a Recordset object, set the Filter or ServerFilter property of a form, or construct an SQL statement. Although each of these operations involves a different syntax, you construct the criteria expression in a similar manner for each.
For example, you can use the DSum function, a domain aggregate function, to find the sum total of all freight costs in the Orders table. You could create a calculated control by entering the following expression in the ControlSource property:
=DSum("[Freight]", "Orders")If you specify the optional criteria argument, the DSum function will be performed on a subset of domain. For example, you could find the sum total of all freight costs in the Orders table for only those orders being shipped to California:
=DSum("[Freight]", "Orders", "[ShipRegion] = 'CA'")When you supply a criteria argument, Microsoft Access first evaluates any expressions included in the argument to form a string expression. Then the string expression is passed to the domain function. The string expression is equivalent to an SQL WHERE clause, without the word WHERE.
You can specify numeric, textual, or date/time criteria. No matter what type of criteria you specify, the criteria argument is always converted to a string before being passed to the domain aggregate function. Therefore, you must make certain that after any expressions have been evaluated, all parts of the argument are concatenated into a single string, the whole of which is enclosed in double quotation marks (").
Use caution when constructing criteria to ensure that the string will be properly concatenated.
The following list of topics outlines the different ways in which you can specify criteria:
Date and Time Criteria Expressions
Change Numeric Criteria from a Control on a Form
Change Textual Criteria from a Control on a Form
Change Date and Time Criteria from a Control on a Form
Multiple Fields in Criteria Expressions