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.