<< . .

. 19
( : 51)

. . >>

7. Click OK.
8. Maximize the Report window.
A blank Report Design window appears (see Figure 8-20). Notice the three sections in the
screen display: Page Header, Detail, and Page Footer. The report is bound to the table
tblProducts. This means that the fields from the table are available for use in the report
design and that they appear in the Field List window. It also means that the data from that
table will be displayed when the report is viewed or printed.

Figure 8-20: A blank Report Design window.
Part I ¦ Getting Functional with Office 2003

Defining the report page size and layout
As you plan your report, consider the page-layout characteristics as well as the kind of
paper and printer you want to use for the output. If you use a dot-matrix printer with a
wide-carriage feed, you design your report differently than for printing on a laser printer
with 8½ x 11-inch paper. After you make these decisions, you use several dialog boxes
and properties to make adjustments; these items work together to create the desired
First, you need to select the correct printer and page-layout characteristics by selecting
File_Page Setup. The Page Setup dialog box enables you to select your printer and set
printer options.
The Page Setup dialog box has three tabs: Margins, Page, and Columns. The information
under the Page tab is divided into three sections:
¦ Orientation. Select the page orientation you want.
¦ Paper. Select the paper size and paper source you want.
¦ Printer. Select the printer you want.

Note If you click the Printer button, the Page Setup dialog box for the selected printer appears.
Clicking Properties will then display a more extensive dialog box with all the applicable options.

The design for Product Summary report is to be a portrait report, which is taller than it is
wide. You want to print on letter size paper that is 8½ x 11 inches, and you want the left,
right, top, and bottom margins all set to 0.250 or the minimum your printer will allow.
Follow these steps to create the proper report setup for the Products Summary report:
1. Open the Page Setup dialog box and select the Page tab.
2. Click the Portrait option button.
Next to the Orientation buttons are two sheet-of-paper icons with the letter A
pictured on them. The picture of the sheet is an indication of its setting.
3. Click the Margins tab.
4. Click the Top margin setting and change the setting to 0.250.
5. Click the Bottom margin setting and change the setting to 0.250.
6. Click the Left margin setting and change the setting to 0.250.
7. Click the Right margin setting and change the setting to 0.250.
Some printers may not allow margins as small as .250 for all four settings. If you
receive a warning, you will need to use a different value.
8. Click OK to close the Page Setup dialog box.
Chapter 8 ¦ Understanding and Creating Access Reports 195

Access displays your reports in Print Preview view by using the driver of the active printer. If you
don™t have a good-quality laser or inkjet printer available for printing, install the driver for one
anyway so that you can view any graphics that you create (and see the report in a high-resolu-
tion display). Later, you can print to your inkjet or other available printer and get the actual hard
copy in the best resolution your printer offers.

After you define your page layout in the Page Setup dialog box, you need to define the size
of your report (which is not necessarily the same as the page definition).
To define the report size, place the mouse pointer on the right-most edge of the report (where
the white page meets the gray background). The pointer changes to a double-headed arrow.
Drag the pointer to change the width of the report. As you drag the edge, a vertical line appears
in the ruler to let you know the exact width if you release the mouse at that point. Be careful
not to exceed the width of the page you defined in the Page Setup dialog box.
When you position the mouse pointer at the bottom of the report, it changes to a double-
headed arrow similar to the one for changing width. Dragging will change the height of the
page footer section or other specified bottom section, not the height of the whole page.
(Predefining a page length directly in the report section doesn™t really make sense because
the detail section will vary in length, based on your groupings.) Remember that the Report
Design view shows only a representation of the various report sections, not the actual report.
To set the right border for the Product Display report to 7½ inches, follow these steps:
1. Click the right-most edge of the report body (where the white page meets the gray
background). The mouse pointer changes to a double-headed arrow.
2. Drag the edge to the 7½-inch mark.
3. Release the mouse button.

You can also change the Width property in the property window for the report.

When you run your report and every other page is blank, it is a sign that the width of your report
Tip exceeds the width of your page. To fix this, decrease your left and right margin size or your
report width. Sometimes, when you move controls around, you accidentally make the report
width larger than your original design. For example, in a portrait report, if your left margin +
report width + right margin is greater than 8½ , you will see blank pages.

Placing fields on the report
Access takes full advantage of Windows™ drag-and-drop capabilities. The method for
placing fields on a report is no exception. As with forms, when you place a field on a
report, it is no longer called a field; it is called a control. A control has a control source (a
specific table field) that it is bound to, so the terms control and field are used
interchangeably in this chapter.
Part I ¦ Getting Functional with Office 2003

To place controls on your report:
1. Display the Field List window by clicking the Field List toolbar button.
2. Click the desired Toolbox control to determine the type of control that will be
created if they are to be different from the default control types for the fields.
3. Select each of the fields that you want on your report and then drag them to the
Report Design window.

Displaying the field list
To display the Field List window, click the Field List button on the toolbar. A small
window with a list of all the fields from the underlying query appears. This window is
called a modeless dialog box because it remains onscreen even while you continue with
other work in Access. The Field List window can be resized and moved around the screen.
The enlarged Field List window is illustrated in Figure 8-21, showing all the fields in the
tblProducts table.

Figure 8-21: Dragging fields to the Design window.

Tip You can move the Field List window by simply clicking on the title bar and dragging it to a new
Chapter 8 ¦ Understanding and Creating Access Reports 197

Selecting the fields for your report
Selecting a field in the Report field list is the same as selecting a field in the Query field list.
The easiest way to select a field is simply to click it. When you click a field, it becomes
highlighted. After a field is highlighted, you can drag it to the Report window.
You can highlight contiguous (adjacent) fields in the list by following these steps:
• Click the first field you want in the field list.
• Move the mouse pointer to the last field you want from the list.
• Hold down the Shift key and click the last field you want.
The block of fields between the first and last field you selected is displayed in reverse video,
indicating it is selected. You can then drag the block of fields to the Report window.
You can highlight noncontiguous fields in the list by clicking each field while holding down
the Ctrl key. Each selected field will be displayed in reverse video; then you can drag the
fields as a group to the Report Design window.

Unlike the Query field list, you cannot also double-click a field to add it to the Report window.

You can begin by selecting the tblProducts fields for the detail section. To select the fields
needed for the detail section of the Product Display report, follow these steps:
1. Click the chrProductID field.
2. Hold down the Shift key and click the curSalePrice field.
The block of fields from chrProductID to curSalePrice should be highlighted in the
Field List window, as shown in Figure 8-20.
3. Hold down the Ctrl key and click the memFeatures field and the olePicture field.
Holding down the Ctrl key lets you select noncontiguous fields. You should have
two blocks of field1.

Dragging fields onto your report
After you select the proper fields from the tblProducts table, all you need to do is drag them
to the detail section of your report. Depending on whether you choose one or several fields,
the mouse pointer changes shape to represent your selection. If you select one field, you see
a Field icon, which shows a single box with some unreadable text inside. If you select
multiple fields, you see a set of three boxes. These are the same icons you saw when you
were using the Query Design screens.
To drag the selected tbProducts table fields into the detail section of the Report Design
window, follow these steps:
1. Click within the highlighted block of fields in the Field List window. You may need
to move the horizontal scroll bar back to the left before starting this process.
Part I ¦ Getting Functional with Office 2003

2. Without releasing the mouse button, drag the mouse pointer into the detail
section; place the icon under the 1½-inch mark on the horizontal ruler at the top
of the screen and next to the ½-inch mark of the vertical ruler along the left edge
of the screen.
3. Release the mouse button.
The fields appear in the detail section of the report, as shown in Figure 8-21. Notice that for
each field you dragged onto the report, there are two controls. When you use the drag-and-
drop method for placing fields, Access automatically creates a label control with the field
name attached to the text control to which the field is bound.

Notice the Bound Object Frame control for the field named Picture. Access always creates a
Bound Object Frame control for an OLE-type object found in a table. Also notice that the detail
section automatically resizes itself to fit all the controls. Above the Bound Object Frame control
is the control for the memo field Features.

You also need to place the desired field controls for the customer information you need in
the page header section. Before you do this, however, you need to resize the page header
frame to leave room for a title you will add later.

Resizing a section
To make room on the report for the title information in the page header, you must resize it.
You can resize a section by placing the mouse pointer at the bottom of the section you want
to resize. The pointer turns into a vertical double-headed arrow; drag the section border up
or down to make the section smaller or larger.
Resize the page header section to make it larger by following these steps:
1. Move the mouse pointer between the bottom of the page header section and the top
of the detail section.
2. When the pointer is displayed as a double-sided arrow, hold down the left mouse
3. Drag the page header section border down until it intersects the detail section™s
ruler at the ¾-inch mark.
4. Release the button to enlarge the page header section.
The page header section expanded to fit the fields that were dragged into the section. All the
fields needed for the Product Display report are now placed in their appropriate sections.

Working with unattached label controls and text
When you drag a field from the Field List window to a report, Access creates not only a data
control but also a label control that is attached to the data control. At times, you will want to
add label controls by themselves to create headings or titles for the report.
Chapter 8 ¦ Understanding and Creating Access Reports 199

Creating unattached labels
To create a new, unattached label control, you must use the Toolbox (unless you copy
an existing label). The next task in the current example is to add the text headers Product
Display and Access Auto Auctions to your report. This task demonstrates adding and
editing text.
To begin creating an unattached label control, follow these steps:
1. Display the Toolbox.
2. Click the Label tool in the Toolbox.
3. Click near the top-left edge of the page header at about the 1/8-inch mark on the
ruler; then drag the mouse pointer downward and to the right to make a small
rectangle about 2½ inches wide and ½-inch high.
4. Type Product Display.
5. Press Enter.
Repeat the process for the label Access Auto Auctions and place it just below the
Product Display label, as shown in Figure 8-22. As you create these label rectangles, it
may make the Page Header section expand.

Tip To create a multiple-line label entry, press Ctrl+Enter to force a line break where you want it in
the control.

If you want to edit or enter a caption that is longer than the space in the property window, the
contents will scroll as you type. Otherwise, open a Zoom box that gives you more space to type
by pressing Shift+F2.

Modifying the appearance of text in a control
To modify the appearance of the text in a control, select the control by clicking its border
(not in the control itself). You can then select a formatting style to apply to the label by
clicking the appropriate button on the Formatting toolbar.
To make the titles stand out, follow these steps to modify the appearance of label text:
1. Click the newly created report heading label Product Display.
2. Click the Bold button on the Formatting toolbar.
3. Click the arrow beside the FontSize drop-down box.
4. Select 18 from the FontSize drop-down list box.
5. Repeat for the Access Auto Auctions label, using a 12 pt font and Bold.
Figure 8-22 shows these labels added, resized, and formatted.
Part I ¦ Getting Functional with Office 2003

Currently, the label rectangles are much large than their displayed text. To tighten the
display or to display all the text when a label rectangle isn™t big enough, you can simply
double-click the bottom left corner handle to resize it (which you will learn more about later
in this chapter).

Figure 8-22: Adding unbound labels to the report.

Working with text boxes and their attached
label controls
So far, you have added controls bound to fields in the tables and unbound label controls used
to display titles in your report. There is another type of text box control that is typically
added to a report: unbound text boxes that are used to hold expressions such as page
numbers, dates, or a calculation.

Creating and using text box controls
In reports, text box controls serve two purposes. First, they enable you to display stored data
from a particular field in a query or table. Second, they display the result of an expression.
Expressions can be calculations that use other controls as their operands, calculations that
use Access functions (either built-in or user-defined), or a combination of the two. You have
learned how to use a text box control to display data from a field and how to create that
control. Next, you learn how to create new text box controls that use expressions.
Chapter 8 ¦ Understanding and Creating Access Reports 201

Entering an expression in a text control
A function is a small program that, when run, returns a single value. The function can be one
of many built-in Access functions or it can be user-defined. For example, to facilitate page
numbering in reports, Access has a function called Page that returns the value of the current
report page. The following steps show you how to use an unbound text box to add a page
number to your report:
1 Click in the middle of the page footer section, resize the page footer so that it is ½-
inch in height, and then create a text box about three-quarters of the height of the
section and about ½-inch wide by resizing the default text box control.
2. Select the Text Box tool on the Toolbox.
3. Scroll down to the page footer section by using the vertical scroll bar.
4. Click the label control to select it. (It should say something similar to Text38.)
5. Click the beginning of the label control text, drag over the default text in the label
control, and type Page: or double-click the text to highlight it and then replace it.
6. Click twice on the text box control (it says “Unbound”); type =Page and press
Enter. (Notice that the Control Source property changes on the data sheet of the
Property window to =[Page]. If the Property window is not open, you may want to
open it to see the change.)
7. Click the Page label control™s Move handle (upper-left corner); move the label
closer to the =[Page] text box control until the right edge of the label control
touches the left edge of the text box control.
Although this is a good exercise for creating labels and text boxes, a better way to add a
page number in the Page Footer section is to use the automatic Page numbers dialog box. To
do this, follow the steps below:
1. Delete the text box you created in the last example from the Page Footer section.
2. Select Insert_Page Numbers¦ from the main menu.
The Page Numbers dialog box is displayed
3. Change the Format to Page N of M.
4. Change the Position to Bottom of Page [Footer].
5. Change Alignment to Right.
Format lets you choose between the final text Page N, where N is the page
number, or Page N of M, where N is the current page number and M is the total
number of pages in the report. It is recommended to always use Page N of M to
make sure the report isn™t missing any pages (or the last page). Position lets you
determine if the page number expression is created in the Page Header or Page
Footer. Alignment lets you determine if the text will be left, right, or centered
aligned. Because this text expression is going to be placed at the bottom right
corner of the report, the Right alignment is preferred. There is also a check box
Part I ¦ Getting Functional with Office 2003

that can be unchecked and lets you eliminate the page number from the first page
(if it were to be used as a cover page).
The completed text box expression looks like this:
=”Page “ & [Page] & “ of “ [Pages]
This would display Page 5 of 25 if page 5 was the current page and there were 25
pages in the report.
The = sign begins an expression. The & symbol (known as concatenation) joins
keywords, fields, or other expressions to a text string. Text strings are surrounded
by double quotes. [Page] and [Pages] are keywords and are surrounded (known as
delimited) by braces ([ ]). Notice the “Page “ text contains a trailing space. This is
done so that there will be a space between the text Page and the current page
number. Notice that there are both leading and trailing spaces in the text string “of.”
Again, this separates the page numbers by a space from the word “of.”

Tip You can always check your result by clicking the Print Preview button on the toolbar and zoom-
ing in on the page footer section to check the page number.

Sizing a text box control or label control
You can select a control by simply clicking it. Depending on the size of the control, from
three to seven sizing handles will appear ” one on each corner except the upper-left corner
and one on each side. When you move the mouse pointer over one of the sizing handles, the
pointer changes into a double-headed arrow. When the pointer changes, click the control and
drag it to the size you want. Notice that, as you drag, an outline appears; it indicates the new
size that the label control will be when you release the mouse button.
If you double-click any of the sizing handles, Access resizes a control to the best fit for the
text in the control. This feature is especially handy if you increase the font size and then
notice that the text is cut off, either on the bottom or to the right. Note that for label controls,
this best-fit sizing resizes both vertically and horizontally, though text controls can resize
only vertically. The reason for this difference is that in the report design mode, Access
doesn™t know how much of a field you want to display; the field name and field contents
might be radically different. Sometimes label controls are not resized correctly, however,
and have to be adjusted manually.

Changing the size of a label control
Earlier in this chapter (in the steps that modified the appearance of label text), you changed
the characteristics of the Product Display label; the text changed, but the label itself did not
adjust. The text no longer fits well within the label control. You can resize the label control,
however, to fit the enhanced font size by following these steps:
1. Click the Product Display label control.
2. Move your mouse pointer over the control. Notice how the pointer changes shape
over the sizing handles.
Chapter 8 ¦ Understanding and Creating Access Reports 203

3. To size the control automatically, double-click one of the sizing handles. The label
control size may still need to be readjusted.
4. Place the pointer in the bottom-right corner of the label control so that the diagonal
double-arrow appears.
5. Hold down the left mouse button and drag the handle to resize the label control
until it correctly displays all of the text (if it doesn™t already).

You can also select Format_Size_To Fit to change the size of the label control text

Before continuing, you should check how the report is progressing. You should do this
frequently as you create a report. You should also save the report frequently as you make
changes to it. You can send a single page to the printer or view the report in print preview.
Figure 8-25 is a zoomed print preview of how the report currently looks. The customer
information is at the top of the page; the pet information is below that and offset to the left.
Notice the title at the top of the page. You can see the page number at the bottom if you click
the magnifying glass button to zoom out and see the entire page. Only one record per page
appears on the report because of the vertical layout. In the next section, you move the fields
around and create a more horizontal layout.

Figure 8-23: A print preview of the report.
Part I ¦ Getting Functional with Office 2003

Deleting and Cutting attached labels from text controls
In order to create the report shown in Figure 8-19, you must remove the label controls from
several of the text box controls and place the label controls in the page header section.
It™s very easy to delete one or more attached controls in a report. Simply select the desired
controls and press Delete. However, if you want to move the label to the page header
section, you can cut the label instead of deleting it. When removing attached controls, there
are two choices:
• Delete only the label control.
• Cut the label control to the clipboard.
• Delete or cut both the label control and the field control.
If you select the label control and press Cut (Ctrl-X) or the Delete key, only the label control
is removed. If you select the field control and press Cut or Delete, both the label control and
the field control are removed. To cut an attached label control (in this case, the Product ID
controls and their attached label), follow these steps:
1. Click the Close button on the toolbar to exit print preview mode. Select the Product
ID label control only in the detail section.
2. Press Ctrl-X (Cut).

<< . .

. 19
( : 51)

. . >>