Access Cookbook - Ken Getz [60]
Discussion
During report design, you are free to reference any "unknown" you'd like as long as you put brackets around it. (If you don't put brackets around it and it's not a field in the underlying record source, Access thinks you entered a string constant and forgot to surround it with quotes, so it puts the quotes in for you.) When you run the report, Access tries to locate the unknown references. If it locates a query parameter or form control that satisfies the reference, it copies the value into the control and continues running the report. If it can't locate the unknown reference, however, it puts up a parameter dialog, requesting help in locating that unknown piece of data.
TIP
If you run a report and get a parameter dialog when you didn't expect one, it's likely that you misspelled either a field name or a reference to a query's parameter.
You can also create parameters directly on reports that are independent of query parameters. For example, you might use this type of "report parameter" if you create a report that requires a person's name and signature at the bottom of a page when you know that the name will vary every time you run the report (and cannot be obtained from the report's record source). Simply add a text box that references the new parameter—for example, [Enter signature name:]. Access will prompt you for this report parameter when you run the report, just as if you had defined the parameter in the report's underlying query.
3.3. Create a Report with Multiple Columns
Problem
You want to print a two-column, phone-book-style report with large initial capital letters to set off each alphabetical grouping. There is no Report Wizard for creating such a report, and you don't see a Column property to set up the number of columns you want. How can you make a multiple-column report in Access?
Solution
There is a way to format a report for multiple columns, but it's not where you might look for it, on a report's properties sheet or the report design menu. Instead, you'll find it on the Columns tab of the Page Setup dialog. This solution guides you through setting up a multiple-column, phone-book-style report that includes a large drop cap for each letter of the alphabet.
Follow these steps to create your own multiple-column report:
Open in design view the report you want to format for multiple columns, and select File → Page Setup. The Page Setup dialog appears. Click on the Columns tab of the Page Setup dialog (see Figure 3-5).
Figure 3-5. The Columns tab of the Page Setup dialog
Enter the appropriate settings for your report. You'll find a brief description of these settings and the settings used for the sample report in Table 3-3. Click OK when you're done.
Table 3-3. The Page Setup dialog Layout settings
Setting
Purpose
Sample
Number of Columns
Number of columns.
2
Row Spacing
Extra space, in inches, between rows.
0
Column Spacing
Extra space, in inches, between columns.
0.25"
Column Size: Width
Width of each column.
3"
Column Size: Height
Height of each column.
1.0625"
Same as Detail
When you check this, Access will copy the width and height of the report's detail section into the Width and Height controls.
Unchecked
Layout Column
Select "Down, then Across" for snaking columns or "Across, then Down" for mailing-label-style columns.
Down, then Across
Leave the report and page headers and footers as they are (if your report has these sections); they will still print across the entire report width.
To keep each name, phone number, and address from breaking inappropriately, set the detail section's KeepTogether property to Yes.
Preview the report; it should now display in two columns.
Follow these additional steps to create the first letter grouping shown in Figure 3-7:
Select View → Sorting and Grouping to display the Sorting and Grouping window. Add the grouping field (in rptPhoneBook, we grouped on Company) twice to the Sorting