<< . .

. 20
( : 51)

. . >>

After you have cut the label, you may want to place it somewhere else. In this
example, you will want to place it into the Page Header section.

Pasting labels into a report section
It is probably just as easy to cut labels from controls placed in the detail section and paste
them into the Page Header as it is to just delete the labels and create new ones in the Page
Header. Regardless, you will now paste the label you have cut in the previous steps:
1. Click anywhere in or on the Page Header section.
2. Press Ctrl-V (Paste).
The Product ID label appears in the Page Header.
3. Repeat for the Description, Category, and Quantity in Stock labels.
4. Delete the remaining label controls in the detail section, leaving all of the text box
If you accidentally selected the data field control and both controls are cut or deleted,
press the Undo toolbar button to undo the action.

If you want to delete only the field control and keep the attached label control, first select the
label control and then select Edit_Copy. Next, to delete both the field control and the label
control, select the field control and press Delete. Finally, select Edit_Paste to paste only the
copied label control to the report.
Chapter 8 ¦ Understanding and Creating Access Reports 205

Moving label and text controls
Before discussing how to move label and text controls, it is important to review a few
differences between attached and unattached controls. When an attached label is created
automatically with a text control, it is called a compound control. In a compound control,
whenever one control in the set is moved, the other control moves as well. With a text
control and a label control, whenever the text control is moved, the attached label is also
moved. Likewise, whenever the label control is moved, the text control is also moved.
To move both controls in a compound control, select one of the pair by clicking the control.
Move the mouse pointer over either of the objects. When the pointer turns into a hand, click
the controls and drag them to their new location. As you drag, an outline for the compound
control moves with your pointer.
To move only one of the controls in a compound control, drag the desired control by its
Move handle (the large square in the upper-left corner of the control). When you click a
compound control, it looks like both controls are selected, but if you look closely, you see
that only one of the two controls is selected (as indicated by the presence of both moving
and sizing handles). The unselected control displays only a moving handle. A pointing
finger indicates that you have selected the Move handles and can now move only one
control. To move either control individually, select the control™s Move handle and drag it to
its new location.

Tip To move a label that is not attached, simply click any border (except where there is a handle)
and drag it.

To make a group selection, click with the mouse pointer anywhere outside a starting point
and drag the pointer through (or around) the controls you want to select. A gray, outlined
rectangle is displayed that shows the extent of the selection. When you release the mouse
button, all the controls that the rectangle surrounds are selected. You can then drag the group
of controls to a new location.

The global option Tools_Options ” Forms/Reports tab ” Selection Behavior is a property
that controls the enclosure of selections. You can enclose them fully (the rectangle must com-
pletely surround the selection) or partially (the rectangle must only touch the control), which is
the default.

Make sure you also resize all of the controls as shown in the figure. The memo field
memFeatures and the OLE picture field olePicture must also be changed in both size
and shape.
Place all of the controls in their proper position to complete the report layout. You want this
first pass at rearranging the controls to look like the example shown in Figure 8-24. You will
make a series of block moves by selecting several controls and then positioning them close
to where you want them. Then, if needed, you fine-tune their position. This is the way most
reports are done.
Part I ¦ Getting Functional with Office 2003

Follow Figure 8-24 to begin placing the controls where they should be. You may want to
notice that the control labels in the Page Header section have been underlined. Also notice
the new label Cost/Retail/Sale Prices in the Detail section.

Figure 8-24: Rearranging the controls on the report.

At this point, you are about halfway done. The screen should look like the one shown in
Figure 8-24. (If it doesn™t, adjust your controls until your screen matches the figure.)
Remember that these screen pictures are taken with the Windows screen driver set at 1024 x
768. If you are using 800 x 600, 640 x 480, or large fonts, you™ll have to scroll the screen to
see the entire report.
These steps complete the rough design for this report. There are still properties, fonts, and
sizes to change. When you make these changes, you™ll have to move fields around again.
Use the designs in Figure 8-19 only as a guideline. How it looks to you, as you refine the
look of the report in the Report window, determines the real design.

Modifying the appearance of multiple controls
The next step is to format all the label controls in the Page Header section directly above the
section separator to be underlined. The following steps guide you through modifying the
appearance of text in multiple label controls:
1. Select all label controls in the bottom of the Page Header section by individually
clicking them while holding down the Shift key. There are four label controls to
select, as shown in Figure 8-24.
You could also have placed your cursor in the vertical ruler at about 1.25 inches
and, when it changed to a right-pointing bold arrow, clicked the mouse to select all
the controls in that horizontal area of the report.
2. Click the Underline button on the toolbar.
Chapter 8 ¦ Understanding and Creating Access Reports 207

You could also have selected all the label controls in the preceding steps by using the drag-and-
surround method.

After you make the final modifications, you are finished, except for fixing the picture
control. To do this, you need to change properties, which you do in the next section. This
may seem to be an enormous number of steps because the procedures were designed to show
you how laying out a report design can be a slow process. Remember, however, that when
you click away with the mouse, you don™t realize how many steps you are doing as you
design the report layout visually. With a WYSIWYG (What You See Is What You Get)
layout like that of the Access report designer, you may need to perform many tasks, but it™s
still easier and faster than programming. Figure 8-24 shows the final version of the design
layout as seen in this chapter.

Changing label and text box control properties
To change the properties of a text or label control, you need to display the control™s property
sheet. If it is not already displayed, perform one of these actions to display it:
¦ Double-click the border of the control (anywhere except a sizing handle or Move
¦ Click the Properties button on the toolbar.
¦ Select View_Properties.
¦ Right-click the mouse and select Properties.
The property sheet enables you to look at a control™s property settings and provides an easy
way to edit the settings. Using tools such as the formatting windows and text-formatting
buttons on the Formatting toolbar also changes the property settings of a control. Clicking
the Bold button, for example, really sets the Font Weight property to Bold. It is usually
much more intuitive to use the toolbar (or even the menus), but some properties are not
accessible this way. In addition, sometimes objects have more options available through the
property sheet.
The Size Mode property of an OLE object (bound object frame), with its options of Clip,
Stretch, and Zoom, is a good example of a property that is available only through the
property sheet.
The Image control, which is a bound object frame, presently has its Size Mode property set
to Clip, which is the default. With Clip, the picture is displayed in its original size and may
be too large to fit in the frame. In this exercise, you will change the setting to Stretch so that
the picture is sized automatically to fit the picture frame.
To change the property for the bound object frame control that contains the picture, follow
these steps:
1. Click the frame control of the picture bound object.
2. Click the Size Mode property.
Part I ¦ Getting Functional with Office 2003

3. Click the arrow to display the drop-down list box.
4. Select Stretch.
These steps complete the changes so far to your report. A print preview of the first few
records appears in Figure 8-25. If you look at the pictures, notice how the picture is properly
displayed and the Features field now appears across the bottom of the detail section. The
labels are all underlined.

Figure 8-25: The report print preview.

Growing and shrinking text box controls
When you print or print-preview fields that can have variable text lengths, Access provides
options for enabling a control to grow or shrink vertically, depending on the exact contents
of a record. The option Can Grow determines whether a text control adds lines to fit
additional text if the record contains more lines of text than the control can display. The
option Can Shrink determines whether a control deletes blank lines if the record™s contents
use fewer lines than the control can display. Although these properties are usable for any
text field, they are especially helpful for memo field controls like the Features control.
Table 8-3 explains the acceptable values for these two properties.
Chapter 8 ¦ Understanding and Creating Access Reports 209

Table 8-3
Text Control Values for Can Grow and Can Shrink
Property Value Description
Can Grow Yes If the data in a record uses more lines than the control is defined
to display, the control resizes to accommodate additional lines.
Can Grow No If the data in a record uses more lines than the control is defined
to display, the control does not resize; it truncates the data
Can Shrink Yes If the data in a record uses fewer lines than the control is
defined to display, the control resizes to eliminate blank lines.
Can Shrink No If the data in a record uses fewer lines than the control is
defined to display, the control does not resize to eliminate blank

To change the Can Grow settings for a text control, follow these steps:
1. Select the Features text box control.
2. Display the Property window.
3. Click the Can Grow property; then click the arrow and select Yes.

Note The Can Grow and Can Shrink properties are also available for report sections. Use a section™s
property sheet to modify these values.

The report is starting to look good, but you may want to see groups of like data together and
determine specific orders of data. In order to do this, you will use sorting and grouping.

Sorting and grouping data
Sorting enables you to determine the order in which the records are viewed in a datasheet,
form, or report, based on the values in one or more fields. This order is important when
you want to view the data in your tables in a sequence other than that of your input. For
example, new products are added to the tblProducts table as they are needed on an
invoice. The physical order of the database reflects the date and time a product is added.
Yet, when you think of the product list, you probably expect it to be in alphabetical order
by Product ID, and you want to sort it by Description of the cost of the product. By
sorting in the report itself, you don™t have to worry about the order of the data. Although
you can sort the data in the table by the primary key or in a query by any field you want, it
is more advantageous to do it in the report. This way, if you change the query or table, the
report is still in the correct order.
You can take this report concept even further by grouping ” that is, breaking related records
into groups. Suppose that you want to list your products first by Category and then by
Part I ¦ Getting Functional with Office 2003

Description within each Category group. To do this, you must use the Category and
Description fields to sort the data. Groupings that can create group headers and footers are
sometimes called control breaks because changes in data trigger the report groups.
Before you can add a grouping, however, you must first define a sort order for at least one
field in the report using the Sorting and Grouping dialog box, which is shown completed in
Figure 8-26. In this example, you use the Category field to sort on first and then the
Description field as the secondary sort.
To define a sort order based on Category and Description, follow these steps:
1. Click the Sorting and Grouping button on the toolbar to display the Sorting and
Grouping box.
2. Click in the first row of the Field/Expression column of the Sorting and Grouping
box. A downward-pointing arrow appears.
3. Click the arrow to display a list of fields in the tblProduct table.
4. Select chrCategory in the field list. Notice that Sort Order defaults to Ascending.
5. Click in the second row of the Field/Expression column.
6. Click the arrow to display a list of fields in the tblProduct table.
7. Select chrDescription in the field list. Notice that Sort Order defaults to Ascending.

Figure 8-26: The Sorting and Grouping box completed.

To see more of the Field/Expression column, drag the border between the Field/Expression and
Sort Order columns to the right.
Chapter 8 ¦ Understanding and Creating Access Reports 211

You can also drag a field from the Field List window into the Sorting and Grouping box Field/
Expression column rather than enter a field or choose one from the field list in the Sorting and
Grouping box Field/Expression column.

Although in this example you used a field, you can alternatively sort (and group) by using
an expression. To enter an expression, click in the desired row of the Field/Expression
column and enter any valid Access expression, making sure that it begins with an equal sign,
as in =[curRetailPrice]-[curCost].
To change the sort order for fields in the Field/Expression column, simply click the Sort
Order column and click the down arrow to display the Sort Order list; then select

Creating a group header or footer
Now that you have added instructions to sort by the Category and Description, you will also
need to create a group header for Category to group all of the products by category. You
don™t need a group footer in this example because there are no totals by category or other
reasons to use a group footer.
To create a group header that enables you to sort and group by the chrCategory field, follow
these steps:
1. Click the Sorting and Grouping button on the toolbar if the Sorting and Grouping
box is not displayed. The field chrCategory should be displayed in the first row
of the Sorting and Grouping box; it should indicate that it is being used as a sort
in Ascending order.
2. Click on the chrCategory row in the Field/Expression column.
3. Click the Group Header property in the bottom pane; an arrow appears.
4. Click the arrow on the right side of the text box; a drop-down list appears.
5. Select Yes from the list. (A header section bar appears on the report.)
After you define a header or footer, the row selection bar changes to the grouping symbol
shown in Figure 8-26. This is the same symbol as in the Sorting and Grouping button on the
toolbar. Figure 8-26 shows both the grouping row symbol and the newly created report
section. The chrCategory header section appears between the page header and detail
sections. If you define a group footer, it appears below the detail section. If a report has
multiple groupings, each subsequent group becomes the one closest to the detail section. The
groups defined first are farthest from the detail section.
The Group Properties pane (displayed at the bottom of the Sorting and Grouping box)
contains these properties:
¦ Group Header. Yes creates a group header. No removes the group header.
¦ Group Footer. Yes creates a group footer. No removes the group footer.
Part I ¦ Getting Functional with Office 2003

¦ Group On. Specifies how you want the values grouped. The options you see in
the drop-down list box depend on the data type of the field on which you™re
grouping. If you group on an expression, you see all the options. Group On has
more choices to make.
For Text data types, there are two choices:
¦ Each Value. The same value in the field or expression.
¦ Prefix Characters. The same first n number of characters in the field.
For Date/Time data types, there are additional options:
¦ Each Value. The same value in the field or expression.
¦ Year. Dates in the same calendar year.
¦ Qtr. Dates in the same calendar quarter.
¦ Month. Dates in the same month.
¦ Week. Dates in the same week.
¦ Day. Dates on the same date.
¦ Hour. Times in the same hour.
¦ Minute. Times in the same minute.
Currency, or Number data types provide three options:
¦ Each Value. The same value in the field or expression.
¦ Interval. Values falling within the interval you specify.
¦ Group Interval. Specifies any interval that is valid for the values in the field or
expression you™re grouping on.
The Group Interval has its own options which include:
• Keep Together. This option controls what™s known as widows and orphans in the
word processing world so that you don™t have a header at the bottom of a page
with no detail until the next page.
• Whole Group. Prints header detail and group footer on one page.
• With First Detail. Prevents the contents of the group header from printing
without any following data or records on a page.
• No. Do not keep together.

After you create the Category group header, you are done with the Sorting and Grouping box for
this report on the CD-ROM that accompanies the Access 2003 Bible. You may need to make
additional changes to groupings as you change the way a report looks; the following three
sections detail how to make these changes. You should not make any of these changes, how-
ever, if you are following the examples or you should press the Save icon now to save the form
in the current state and then discard the changes done to this form after this point.
Chapter 8 ¦ Understanding and Creating Access Reports 213

Changing the group order
Access enables you to easily change the Sorting and Grouping order without moving all the
individual controls in the associated headers and footers. Here are the general steps to
change the sorting and grouping order:
1. Click the selector bar of the field or expression you want to move in the Sorting and
Grouping window.
2. Click the selector again and hold down the left mouse button.
3. Drag the row to a new location.
4. Release the mouse button.

Removing a group header or footer
To remove a page or report header/footer section, use the View_Page Header/Footer and
View_Report Header/Footer toggles. To remove a group header or footer while leaving the
sorting intact, follow these steps:
1. In the Sorting and Grouping window, click the selector bar of the field or expres-
sion that you want to remove from the grouping.
2. Click the Group Header text box.
3. Change the value to No.
4. Press Enter.
To remove a group footer, follow the same steps, but click Group Footer in Step 2.
To permanently remove both the sorting and grouping for a particular field (and thereby
remove the group header and footer sections), follow these steps:
1. Click the selector of the field or expression you want to delete.
2. Press Delete. A dialog box appears asking you to confirm the deletion.
3. Click OK.

Hiding a section
Access also enables you to hide headers and footers so that you can break data into groups
without having to view information about the group itself. You can also hide the detail
section so that you see only a summary report. To hide a section, follow these steps:
1. Click the section you want to hide.
2. Display the section property sheet.
3. Click the Visible property™s text box.
4. Click the drop-down list arrow on the right side of the text box.
5. Select No from the drop-down list box.
Part I ¦ Getting Functional with Office 2003

Sections are not the only objects in a report that can be hidden; controls also have a Visible
property. This property can be useful for expressions that trigger other expressions.

Sizing a section
Now that you have created the group header, you might want to put some controls in the
section, move some controls around, or even move controls between sections. Before you start
manipulating controls within a section, you should make sure the section is the proper height.
To modify the height of a section, drag the border of the section below it. If, for example,
you have a report with a page header, detail section, and page footer, change the height of
the detail section by dragging the top of the page footer section™s border. You can make a
section larger or smaller by dragging the bottom border of the section. For this example,
change the height of the group header section to 3/8 inch with these steps:
1. Move your mouse pointer to the bottom of the chrCategory section. The pointer
changes to a horizontal line split by two vertical arrows.
2. Select the top of the detail section (which is also the bottom of the chrCategory
Header section).
3. Drag the selected band lower until three dots appear in the vertical ruler (3/8”).
The gray line indicates where the top of the border will be when you release the
mouse button.
4. Release the mouse button.

Moving controls between sections
You now want to move the chrCategory control from the Detail section to the chrCategory
Header section. You can move one or more controls between sections by simply dragging
the control with your mouse from one section to another or by cutting it from one section
and pasting it to another section. Follow the instructions below to move the chrCategory
control from the Detail section to the chrCategory section:
1. Select the chrCategory control in the Detail section.
2. Drag the chrCategory control up to the chrCategory Header section and drop it
close to the vertical ruler, as shown in Figure 8-27.
3. Release the mouse button.
4. Press the Underline button to underline the chrCategory control to further
highlight it as a group header. Sometimes, you might want to bold it or even
increase the font size.
You should now do the following steps to complete the report design:
1. Delete the Category label from the Page Header.
2. Move the chrProductID control and its associated label after the chrDescription
control and its associated label, as shown in Figure 8-27.
Chapter 8 ¦ Understanding and Creating Access Reports 215

3. Move the chrDescription control and its associated label to the left so that it starts
just to the right of the start of the chrCategory control in the chrCategory Header

<< . .

. 20
( : 51)

. . >>