Microsoft Office Tutorials and References
In Depth Information
Oren wants you to change the column headings of -1 to Paid and of zero to Unpaid.
You’ll use the IIf function to change the column headings, using the expression IIf
(InvoicePaid,”Paid”,”Unpaid”) —if the InvoicePaid field value is true (because it’s a Yes/No
field or a True/False field), or is checked, use Paid as the column heading; otherwise, use
Unpaid as the column heading.
Because the InvoicePaid
field is a Yes/No field, the
condition InvoicePaid is
the same as the condition
InvoicePaid = −1 , which
uses a comparison operator
and a value. For all data
types except Yes/No fields,
you must use a comparison
operator in a condition.
To change the crosstab query column headings:
1. Click the Home tab on the Ribbon, and then switch to Design view. The design grid
has four entries. See Figure 5-24.
Crosstab query in the design grid
heading and values
From left to right, the City entry produces the row headings in the crosstab query,
the InvoicePaid entry produces the column headings, the InvoiceAmt entry pro-
duces the totals in each row/column intersection, and the Total Of InvoiceAmt
entry produces the row total column heading and total values. Each field name is
enclosed in brackets.
You need to replace the Field text box value in the second column with the IIf function
expression to change the -1 and zero column headings to Paid and Unpaid. You can
type the expression directly in the text box, use Expression Builder to create the
expression, or type the expression in the Zoom dialog box. You’ll use the last method.
2. Click the InvoicePaid Field text box, and then open the Zoom dialog box.
3. Delete the highlighted expression, and then type IIf (InvoicePaid,"Paid",
"Unpaid") in the Zoom dialog box. See Figure 5-25.
IIf function for the crosstab query column headings
4. Click the OK button, and then save and run the query. Access displays the com-
plete crosstab query with Paid and Unpaid as column headings.
You can now close the completed query.
5. Close the query, and then open the Navigation Pane.