Microsoft Office Tutorials and References
In Depth Information
Chapter 4: Doing Calculations in Forms and Subforms (and Reports)
Don’t give the text box control the same name as a field in the record source
for the form or report! If the table or query that provides the records has a
field called Full Name, for example, don’t create a calculated text box with
that name. Two objects with the same name confuse Access; if you refer to
that name, Access won’t know whether you want the field or the control.
Checking your expression
After you type an expression in the Control Source property of a text box
(or use the Expression Builder to create it), you see the expression itself in
the text box. What about the answer?
To check whether the expression works, switch to Form view by clicking the
View button on the Design or Home tab of the Ribbon. (For reports, switch
to Print Preview.) Check the answer in several records to see whether the
expression works as you expect.
If you make a mistake in your expression, you may see one of three things in
Form view or Print Preview: a wrong answer, #Name?, or another error
message that starts with #. If you find an error, check out these ideas for fixing
your calculated text box and its expression:
✦ #Name? indicates that Access can’t understand a field name in your
expression. The most likely reason is that you forgot the equal sign (=)
at the beginning of the expression. Alternatively, you may have
misspelled a field name or forgotten to enclose it in square brackets. If your
text box control has the same name as a field, Access can’t tell which
one you’re referring to, so check the name of the text box too. (It’s the
Name property on the All tab of the property sheet.)
✦ #Div/0! means that you’re dividing something by zero, which is
impossible in standard arithmetic. Check the fields in your expression to see
whether 1 in the denominator might be 0 for some records.
✦ #Error indicates some other problem; check the expression carefully.