Troubleshoot expressions

Unexpected results

Although using expressions is relatively straightforward, you can occasionally get unexpected results when you:

Combine (concatenate) control or field values, variables, or text in an expression.

Concatenating control or field values or text in an expression might produce unexpected results for any of the following reasons:

You're referring to a control or field that has the same name as the current control, creating a circular reference. Change the name of the field or control.
You're referring to a control or field using a name that is misspelled or typed incorrectly. Verify that it's entered correctly.
You're referring to a control or field that has the same name as a property. Use the full identifier for the control or field.
Microsoft Access isn't evaluating the expression correctly because part of the expression must be surrounded by brackets ([ ]) or single (') or double (") quotation marks. Check to see whether bracket or quotation mark characters are required.

Perform operations on fields that have blank values.

Many operations might produce unexpected results if they use an expression that refers to a field that has blank (Null) values. For example, the DCount or DSum functions might produce unexpected results if they use criteria expressions that refer to a field with Null values.

To correct this problem, you can either:

Pass field values to the Nz function in the expression. When the Nz function evaluates a Null value, the function returns zero or a zero-length string.
Use the IIf and IsNull functions to test whether a value is Null, and then return an appropriate value.

Enter a date value in an expression.

Microsoft Access can produce unexpected results if you enter a date value in an expression without surrounding it with number signs (#) for ANSI-89 or single (') quotation marks in ANSI-92. Note that Access 2002 and later conforms to ANSI-92.

Refer to a subform or subreport property or control.

Microsoft Access can produce unexpected results if you refer to a subform or subreport property or control by using an incorrect identifier.

To refer to a subform or subreport property, you must type a full identifier for the form or report by using the Form or Report property of a subform or subreport control. For example, the following identifier refers to the Visible property of the Orders Subform subform:

Forms![Orders]![OrdersSubformControl].Form.Visible

Expression	Refers to
Forms![Orders]![OrdersSubformControl]	The subform control that contains the subform.
Forms![Orders]![OrdersSubformControl] .Form	The subform itself. You must specify the Form property to refer to the subform itself or one of its properties.

In contrast, to refer to a control on a subform or subreport, or one of the control's properties, you don't have to use the Form or Report property. For example, the following identifier refers to the Visible property of the Discount control on the Orders Subform subform:

Forms![Orders]![OrdersSubformControl]![Discount].Visible

Refer to a column in a list box or combo box

To refer to a column other than the bound column, use the Column property. The Column property is zero-based. For example, to refer to the second column in the combo box called SuppliersCombo on the Products form, use this syntax:

Forms![Products]![SuppliersCombo].Column(1)

Expression Builder

I can't find a field name in the Expression Builder.

To view new field names in the Expression Builder, you must first save the table or query containing the new fields.

I can't find the object or function I want in the Expression Builder.

If an object or function isn't listed in the lower section of the Expression Builder, it's not valid in the context where you started the builder. For example, you can't refer to other fields and controls in the validation rule for a field in table Design view, so the Tables, Queries, Forms, and Reports folders aren't available if you start the Expression Builder from the ValidationRule property of a field in table Design view.