Troubleshoot expressions

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.

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.

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

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

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)

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

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.