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:
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.