Australian PC World - Make exceptions to the rule with IF functions

Make exceptions to the rule with IF functions

Tip
One pesky thing about designing a database is deciding how to handle exceptions to the rules. Suppose you want to send a mailing to customers that gives them an opportunity to receive a 10 per cent discount on orders they place in the next few weeks. This would be a simple project except that you have a few preferred customers to whom you would like to offer a discount of 20 per cent.
There are a couple of approaches. You can design two mailing pieces from two different database reports -- one that shows a 10 per cent discount for regular customers and another that offers 20 per cent off to preferred accounts. This solution has two drawbacks: you have to include a filter in each report that selects the right customers, and you have to maintain two reports in the database.
A better solution is to design a single report that uses an If function, which allows you to print a subset of data for each condition. In this case, it displays a discount percentage that depends on the contents of a flag field called Preferred in the Customer table.

Access: embed an IIf() formula in your letter

1. Add a yes/no type field named Preferred to your Customer table. Then enter Yes in this field for any records for preferred customers.
2. Design a new, blank report for the letter. You can also use the Report Wizard to create a tabular report and rearrange the fields to create the letter's heading.
3. Choose View--Toolbox and click the Label tool to add a box for the body of the letter. Then enter the letter's text.
4. Use the Text Box button in the Toolbox to add a control to the design to show the percentage discount. Place the text box in the Label object that contains the body of the report. Right-click the text box, choose Properties, click the Data tab, and enter =IIf([Preferred]=Yes,"20%","10%") on the Control Source line (see Step by Step). You can omit =Yes if you like. Access assumes that referring to a Yes/No field by name in an IIf() expression automatically tests whether the field is set to Yes.
5. Save the report and view it for preferred and non-preferred customers.
The IIf() expression has three parts. The first part is a statement that Access evaluates to determine whether it is true or false. In our example, the IIf() expression looks for Yes in the Preferred field of a customer record. The second part, 20%, tells Access what value to display in the text box if the first expression is true. The third part, 10%, says what value should be used when the first expression is false. In this case, the first statement is false when the Preferred field is set to No.

Approach: add a discount amount field to the letter

As in the Access example, you need to add a field showing whether a customer is preferred. Open your Customer file, choose Create--Field Definition, and add a Boolean field called Preferred. Also add a Calculated field called Discount Amount. In the Formula box, enter If(Preferred,'20%','10%'), and then click OK. Add the Preferred field to the form you use to update customer records, and enter Yes for preferred customers.
Choose Create--Form Letter and step through the Form Letter Assistant. Type in the body of the letter, and enter <<"Discount Amount">> where you want the discount to appear. When you view the letter in Print Preview, you should see the correct discount.
- Celeste Robinson

Category: Data management
Issue: May 1997
Pages: 170