Export (0) Print
Expand All

Numeric Criteria from a Control on a Form

Last modified: June 29, 2011

Applies to: Access 2013 | Office 2013

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 text box containing the EmployeeID number.

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

Suppose that you are performing the DLookup function on an Employees table to find the last name of an employee based on the EmployeeID number. In the following example, the criteria is determined by the current value of the EmployeeID control on the Orders form. The expression referencing the control is evaluated each time the function is called, so that if the value of the control changes, the criteria argument will reflect that change.

=DLookup("[LastName]", "Employees", "[EmployeeID] = " _ 
 & Forms!Orders!EmployeeID)

If the current value of the EmployeeID field is 7, the criteria argument that is passed to the DLookup function is:

"[EmployeeID] = 7"
TipTip

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 numeric value in the criteria argument. The variable should be separate from the string expression, so that Microsoft Access will evaluate the variable first and then concatenate it with the rest of the string expression.

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

Dim intNum As Integer 
Dim varResult As Variant 
 
intNum = 7 
varResult = DLookup("[LastName]", "Employees", _ 
 "[EmployeeID] = " & intNum)
Show:
© 2014 Microsoft