Enterprise Software

Customize merged Word docs/Create Excel check boxes/Delete default shortcuts in Access

Customize the contents of a merge document based on a value

Have you ever wished you could customize the contents of a merge letter based on the values stored in a merge field? You can do just that with the If...Then...Else... merge field.

Let's say that in your monthly statement to customers, you want to show the number of transactions for the month, a number stored in your data source in a field called TransCount. If the value in TransCount is 1, you want to print "1 transaction." If the value in TransCount is 0 or greater than 1, you want to make the word "transaction" plural, as in "0 transactions" or "22 transactions."

Follow these steps:

  1. Go to Tools | Mail Merge, and edit your main document.
  2. On the Mail Merge toolbar, click the Insert Merge Field button, and select TransCount.
  3. Press the spacebar after the <<TransCount>> field, and type transaction in the main document.
  4. Click the Insert Word Field button, and choose If...Then...Else...
  5. Select TransCount from the Field Name drop-down list, choose Equal To in the Comparison drop-down list, and type 1 in the Compare To field. (Leave the Insert This Text field blank.)
  6. In the Otherwise Insert This Text field, type s, and click OK.

When you run the mail merge, the If...Then...Else... field will return nothing when the value in TransCount is 1, so the phrase in the merge document will appear as "1 transaction." In all other cases, the field will return the letter "s," which turns "transaction" into "transactions."

Create check boxes

Are you looking for a great way to get a yes or no decision from an end user? Try presenting your question in the form of a check box. If the user selects the box, the answer is yes. You can store the result of the decision as a value in a cell and use that value in your code or calculations.

To see how the check box works, open a blank worksheet, go to View | Toolbars, and choose Forms. Click the Check Box tool once, and click and drag in the worksheet to draw a check box. When you release, click the Forms toolbar's Control Properties tool, or right-click the new check box, and choose Format Control.

You'll have plenty of time later to tweak the text associated with the check box. Your first order of business is to tell Excel where to store the check box's "state." On the Control tab, enter A1 in the Cell Link field, and click OK.

When you select the check box, you'll see the value in A1 change to TRUE when checked and to FALSE when unchecked. You can use that value in your formulas. For example, you can display a custom message with a formula such as the following:

=IF(A1,"Checked message","Unchecked message")

By the way, the check box Control Properties also let you set the initial value (state) for the check box. If you choose Mixed, the check box will initially appear grayed out and return a value of #N/A to the linked cell, which tells you that the item is neither checked nor unchecked.

Delete default shortcuts

Access 2000 provides shortcuts that let you create new objects just by double-clicking them. For example, when you click Tables in the Objects list, by default you'll see three shortcuts in the database window: one for creating a table in Design view, one for creating a table with the wizard, and one that lets you create a table by entering data.

When you select Queries, Forms, or Reports, you'll see only two shortcuts: one for working in Design view and one for launching a wizard. Click Pages, and you'll see the Design view and wizard shortcuts, plus a shortcut that launches the Locate Web Page dialog box.

By the time you finish most of your initial design work, your database window may be crowded. You can eliminate some clutter and make it easier to locate the objects you use on a regular basis by getting rid of the default shortcuts.

To do so, go to Tools | Options | View. In the Show section, deselect the New Object Shortcuts check box, and click OK. When you do, Access stops displaying those shortcuts for all databases.

Editor's Picks