Include parameter values in your Access report titles

You can create more informative titles for reports that are based on parameter queries. This simple technique will enable you to include the parameters in your report titles and headers.

---

For reports based on parameter queries, it is helpful if the parameters used to generate the report are displayed in the report title. You just need to create a text box next to the report title and enter =Reports![reportname]!parametername] as the Text box’s control source.

For example, suppose you have created a report called Hours Worked for Week Ending based on the Parameter query shown in Figure A.

Figure A

To add to your title the date that’s input into the parameter query box, follow these steps:

1. Add a text box to the end of the report title.
2. Right-click the text box and select Properties.
3. Click in the Control Source property and enter =Reports![Hours Worked for Week Ending]![Enter Week Ending Date] (Figure B).
4. Format the text box to match the rest of the Report title (Figure C).

Figure B

Figure C

When the report is run, Access displays the date entered for the parameter in the text box following the report title (Figure D) . You can also place the text box displaying the report’s parameters in the report header.

Figure D

---

Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Create a watermark using a Clip Art Gallery image

While you can use Word’s Printed Watermark dialog box to add a custom watermark to your document, Word also lets you create a watermark from any graphic object (SmartArt, charts, shapes, clip art, etc.) by simply copying the graphic into the Header window. Follow these steps to create a watermark from a copy of a picture taken from the Clip Art Gallery:

1. Open a blank document.
2. Go to Insert |Clip Art. (In Word 2007, click the Insert tab and select Clip Art in the Illustrations group.)
3. Search for the desired clip art in the Clip Art task pane.
4. Go to View | Header or footer. (In 2007, double-click the top of the page to access the Header area.)
5. Click inside the Header window.
6. In the Clip Art task pane, double-click the clip art picture you want as your watermark.
7. Right click the portion of the picture in the header window and select Text Wrapping.

1. Click the Behind Text option.
2. Right-click the portion of the picture in the Header window and then select Send To Back.
3. Click Send Behind Text.
4. Click and drag the bottom-right picture handle to extend the picture into the middle of the document beyond the header.

You can also format the picture to make it more transparent. For example, in Word 2007, follow these steps:

1. Double-click the Header to display the Header window.
2. Right-click the picture in the Header window and then select Format Picture.
3. Click the drop-down arrow of the Recolor button and click the first selection under Light variations. (Alternatively, you can choose Washout under Color Modes.)
4. Click Close.

---

Miss a Word tip?

Check out the Microsoft Word archive and catch up on other Word tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Avoid costly errors by verifying cross-foot results in Excel

Cross-footing can help keep your worksheet healthy, but comparing subtotals can only go so far. See how a simple IF function can alert you to problems with your data.

---

Accuracy is vital. If accuracy didn’t matter, you’d rely on your fingers instead of on Excel to sum your transactions. Unfortunately, Excel is just as vulnerable to mistakes as your calculating fingers. To verify subtotals and totals, auditors use a technique called cross-footing, which works great until someone erroneously deletes or overwrites a dependent formula. If something like that happens, you have to visually catch the discrepancy. To ensure that you don’t miss such a mistake, use a cross-footing formula that verifies itself.

Note: This article is also available as a PDF download.

Some definitions

Cross-footing is a modern term that has evolved to mean more than it originally did. Footing is the act of totaling columns. Cross-footing originally meant to total values in a row. However, today, the term refers to the overall process of double-checking totals by comparing columnar and row subtotals. Figure A shows cross-footing at work. The formula in cell F6 totals the row subtotals. The formula in cell E7

=SUM(B6:E6)

sums the column subtotals. When all is right, the totals are the same.

Figure A

Compare subtotals to verify accuracy.

Figure B shows what happens when someone deletes (or overwrites) one of the subtotals. The cross-foot totals don’t agree. With a quick glance, you can see that something’s wrong.

Figure B

When subtotals don’t match, you know something’s wrong.

---

The obvious but impractical solution

Since verifying cross-foot results involves dependent formulas, protecting those cells that contain dependent formulas seems like the most obvious way to ensure the accuracy of your spreadsheet. It works well, but it’s impractical if more than one person can update the spreadsheet. When that’s the case, those people have the password to unprotect the file and while modifying the spreadsheet, anything can happen. By all means, apply protection, but don’t rely on it solely.

---

Verifying cross-foot formulas

Although the two formulas alert you to a problem, the responsibility is on you to notice that the values differ. That’s a lot of responsibility, but a simple IF function can help by alerting you in a more obvious way. Let’s return to the sample worksheet in Figure A to employ a cross-foot formula that verifies itself. In this case, you’d use a formula that compares both sets of subtotals using the following syntax:

=IF(SUM(rowsubtotal)=SUM(columnsubtotal), SUM(eithersubtotal), “warningtext“)

If a user accidentally overwrites or even deletes a subtotal, the verifying formula will warn you, as shown in Figure C.

Figure C

Use text to warn you when subtotals don’t match.

Now, the warning text goes a long way toward getting your attention, but you can improve the effect by applying a conditional format as follows:

1. Select the cell that contains the verifying formula. In the case of the example spreadsheet, you’d select cell F6.
2. Choose Conditional Formatting from the Format menu.
3. Choose Cell Value Is from the first drop-down list. (This is the default, so you shouldn’t have to do anything.)
4. Choose Equal To from the second drop-down list.
5. In the third control, type warningtext exactly as it appears in the formula. In this case, you’d enter “SUMS DO NOT BALANCE,” as shown in Figure D.
6. Click the Format button and set the formatting you want when the verifying formula returns warningtext. In this case, click the Font tab and choose Bold from the Font Style list boox nd Red from the Color drop-down list.
7. Click OK twice.

Figure D

Enter the exact warningtext, as used in the verifying formula.

As you can see in Figure E, the warning text stands out and gets the message across. You can apply more conditional formats. Just let your spreadsheet’s existing formats dictate the right effect and don’t hesitate to mix things up a bit.

Figure E

The red warning text is hard to miss.

Don’t be at cross purposes: Verify!

Cross-footing is a good way to keep your spreadsheets healthy, but comparing subtotals can only go so far. Use the simple IF function reviewed in this article to display an obvious and meaningful warning when something’s wrong.

Copy an Excel worksheet into a new workbook file

If you update recurring data, such as monthly sales figures, you might create a copy of a completed sheet to use as a starting point. For instance, when you’re ready to start tracking February’s sales, you might copy January’s sheet, delete January’s figures, and start entering February’s. Copying a sheet is easy to do. You simply select the sheet you want to copy and choose Move Or Copy Sheet from the Edit menu. This feature quickly makes a copy of the sheet within the same workbook.

What you might not realize is that you can use this same feature to copy a sheet from one workbook to another. The capability’s pretty obvious, but the truth is, if you don’t need it, you don’t realize it’s there because it’s easy to overlook. To copy a sheet into a new workbook file, do the following:

1. Select the sheet you want to copy (or move).
2. Choose Move Or Copy Sheet from the Edit menu.
3. In the Move Or Copy dialog, select (new book) from the To Book list box.

1. Be sure to check the Create A Copy option if you want to copy instead of move the sheet.
2. Click OK.

Excel will open a new workbook with the copied sheet. If you’re using Excel 2007, do the following:

1. Select the tab of the sheet you want to copy (or move).
2. Click the Home tab and then click Format in the Cells group.
3. In the Organize Sheets section, click Move Or Copy Sheet.
4. In the Move Or Copy dialog, select new book from the To Book list box.
5. Be sure to check the Create A Copy option if you want to copy instead of move the sheet.
6. Click OK.

Use the same abbreviations to create different AutoCorrect entries

Using abbreviations is an intuitive way to handle AutoCorrect entries. They’re easy to remember for most of us. For instance, you might use your initials to represent your name in an AutoCorrect entry. That arrangement works well until you run across an abbreviation that might work for more than one entry. For instance, the abbreviation htg might work for Harkins Technological Group and HTG International.

Now, you might think that you can’t use htg for both, but you can by prefixing one with an apostrophe character (’). For instance, you might enter htg for the company name and ‘htg for the abbreviated version.

To access this feature, choose AutoCorrect Options from the Tools menu. Enter the abbreviation in the Replace field and the replacement text in the With field.

To use the AutoCorrect entries, type htg and Word will automatically replace htg with Harkins Technological Group. Word will change ‘htg to HTG, International.

You could also use two different entries — say, htg and htginc. There’s nothing wrong with that solution, but it does require a few more keystrokes. You’ll find the apostrophe solution particularly useful when entries are closely related to specific abbreviations.

Office Challenge: How do you print Excel comments?

If your spreadsheets are anything like mine, they store a number of comments. Printing some or all of those comments can be a bit tricky. How do you print Excel comments?

Last week, we asked…

“How do you move Word table rows up or down?” The keyboard provides the quickest method:

1. Click inside the row you want to move.
2. Hold down [Alt]+[Shift].
3. Press the up or down arrow, appropriately.

For instance, if you want to move the fourth row to the second row, you’d click inside row 4, hold down [Alt]+[Shift], and click the up arrow twice. Doing so moves row 4 up to row 2, pushes the second row down to row three, the third row to row four, and so on. In addition, you can select multiple rows and move them all at once.

Put your photos into PowerPoint

Take this comprehensive training course on how to insert pictures into a presentation, create a photo album, and even apply frames or color effects.

Community tutorial: Add a showcase navigation to your PowerPoint 2007 presentation

How can you turn a linear slideshow into a freeform presentation? By adding navigation to your PowerPoint file, you can adjust your message while you present.

Animated text effects for PowerPoint slides

Check out these professionally-crafted slides featuring sparkling title slides, animated bulleted lists, and moving timelines.

Quickly add page numbers to your Word documents

Sometimes, it’s more efficient to take a task into your own hands. See how you can save a little time entering a PAGE field instead of making a trip to the Page Numbers dialog box.

---

If all you need is a page number at the top or bottom of your page, you don’t have to spend time clicking through options to insert it. It may be faster to enter a Page field in the header or footer. For example, to insert the page number at the top-right of each page in your document, follow these steps:

1. Go to View | Header And Footer. In Word 2007, double-click at the top of the page to display the header.
2. Click the Align Right button on the Formatting toolbar. In Word 2007, click the Align Text Right button in the Paragraph group on the Home tab.
3. Type the word PAGE (Figure A) and select it.
4. Press Ctrl +  F9 to insert the field (Figure B).
5. Press F9 to update the field.
6. Double-click outside the Header pane to return to the body of the document.

Figure A

Figure B

---

Miss a Word tip?

Check out the Microsoft Word archive and catch up on other Word tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Next Page »