Microsoft Office Tutorials and References
In Depth Information
To display the correct customer value, you’ll use the IIf function. The IIf (Immediate
If) function assigns one value to a calculated field or control if a condition is true, and a
second value if the condition is false. The IIf function has three parts: a condition that is
true or false, the result when the condition is true, and the result when the condition is
false. Each part of the IIf function is separated by a comma. The condition you’ll use is
IsNull(Company) .The IsNull function tests a field value or an expression for a null value;
if the field value or expression is null, the result is true; otherwise, the result is false. The
expression IsNull(Company) is true when the Company field value is null, and is false
when the Company field value is not null.
For the calculated field, you’ll enter IIf(IsNull(Company),LastName & “, “ &
FirstName,Company) . You interpret this expression as: If the Company field value is null,
then set the calculated field value to the concatenation of the LastName field value and
the text string “, “ and the FirstName field value. If the Company field value is not null,
then set the calculated field value to the Company field value.
Now you are ready to create Sarah’s query to display the customer name.
To create the query to display the customer name:
1. Click the Create tab on the Ribbon and then, in the Other group on the Create tab,
click the Query Design button to open the Show Table dialog box on top of the
Query window in Design view.
2. Click tblCustomer in the Tables list box, click the Add button, and then click the
Close button to place the tblCustomer table field list in the Query window and
close the Show Table dialog box.
Sarah wants all fields from the tblCustomer table to appear in the query recordset
and the new calculated field to appear in the first column.
3. Double-click the title bar of the tblCustomer field list to highlight all the fields,
and then drag the highlighted fields to the second column’s Field text box in the
design grid. Access places each field in a separate column in the design grid start-
ing with the second column in the design grid, in the same order that the fields
appear in the table.
Trouble? If you accidentally drag the highlighted fields to the first column in the
design grid, click the CustomerID Field text box, and then click the Insert Columns
button in the Query Setup group on the Query Tools Design tab. Continue with Step 4.
4. Right-click the blank Field text box to the left of the CustomerID field, and then
click Build on the shortcut menu to open the Expression Builder dialog box.
Sarah wants to use “Customer” as the name of the calculated field, so you’ll type
that name, followed by a colon, and then you’ll choose the IIf function.
5. Type Customer: and then press the spacebar . Be sure you type the colon follow-
6. Double-click Functions in the left column, click Built-In Functions in the left col-
umn, scroll down the middle column and click Program Flow , click IIf in the right
column, and then click the Paste button. Access adds the IIf function with four
placeholders to the right of the calculated field name in the expression box. See
After clicking in a text box,
you can also open the
Expression Builder dialog
box for that text box by
holding down the Ctrl key
and pressing the F2 key.