Concatenating control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) 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 (identifier (expressions): An element of an expression that refers to the value of a field, control, or property. For example, Forms![Orders]![OrderID] is an identifier that refers to the value in the OrderID control on the Orders form.) 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 (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.)) values. For example, the DCount or DSum functions might produce unexpected results if they use criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) 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 (zero-length string: A string that contains no characters. You can use a zero-length string to indicate that you know no value exists for a field. You enter a zero-length string by typing two double quotation marks with no space between them (" ").).
• 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 (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) by using an incorrect identifier (identifier (expressions): An element of an expression that refers to the value of a field, control, or property. For example, Forms![Orders]![OrderID] is an identifier that refers to the value in the OrderID control on the Orders form.).

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 (validation rule: A property that defines valid input values for a field or record in a table, or a control on a form. Access displays the message specified in the ValidationText property when the rule is violated.) for a field in table Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), 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.