Edit

Textual criteria from a control on a form

  • Article

If you want to change the criteria argument for an operation based on a user's decision, you can specify that the criteria comes from a control on a form. For example, you could specify that the criteria argument comes from a list box containing the last names of all employees in an Employees table.

To specify textual criteria coming from a control on a form, you include in the criteria argument an expression that references the control on the form. This expression should be separate from the string expression, so that Access will evaluate the control expression first and concatenate it with the rest of the string expression before performing the appropriate operation.

In addition to enclosing the entire string expression in double quotation marks ("), you must also ensure that the textual criteria within the string expression is enclosed in single quotation marks ('). The quotation marks must be included in the strings flanking the expression that references the control on the form.

Note

The single quotation marks indicate to Access that the criteria argument contains a string within a string.

The following example performs a lookup on an Employees table and returns the region in which an employee lives, based on the employee's last name. The current value of a list box control called LastName on the Employees form determines the criteria. Note the placement of the single quotation marks.

=DLookup("[Region]", "Employees", "[LastName] = '" _ 
 & Forms!Employees!LastName & "'")

If the current value of the control is King, the following criteria argument is passed to the DLookup function after Access evaluates the expression and concatenates the strings:

"[LastName] = 'King'"

Keep in mind that the entire string comprising the criteria argument must also be enclosed in double quotation marks after the strings have been concatenated.

Tip

To troubleshoot an expression in the criteria argument, break the expression into smaller components and test each individually in the Immediate window. When all of the components are working correctly, put them back together one at a time until the complete expression works correctly.

You can also include a variable representing a textual string in the criteria argument. The variable should be separate from the string expression, so that Access will evaluate the variable first and then concatenate it with the rest of the string expression. The textual string must be enclosed in single or double quotation marks.

The following example shows how to construct a criteria argument that includes a variable representing a textual string:

Dim strLastName As String 
Dim varResult As Variant 
 
strLastName = "King" 
varResult = DLookup("[EmployeeID]", "Employees", "[LastName] = '" _ 
 & strLastName & "'")

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.