You already learned a lot about managing
controls in the lesson Controls in the
section on forms. You worked with multiple controls at once in the
lesson Creating a Form Manually.
The same methods for selecting, formatting, aligning, and moving controls
also work on reports. (If you skipped those
lessons, you might want to review them now.)
Where to Make Adjustments
Getting a report laid out just right can be a frustrating experience.
When controls are not quite lined up evenly, it is more obvious in a report on paper than
it is in a form on the screen. Lines may print jagged if they
are slanted. You cannot always tell from the Print Preview. The rendering on screen may not
quite match what prints.
- Page Setup
- Page Margins - Default is 1" on all sides.
- Columns - Number of columns; width of columns (usually the
same as the
width of Detail); column down then across or across and then down
- Page Orientation - Portrait or Landscape
- Report Design View
- Width -All sections use the same width as the report. To
reduce the width by dragging the edge or in the report's Properties, you
must first move or resize any controls that are in the way.
- Sections - Which sections are shown
- Section height - Reduce to fit controls. Set Can Grow/Can
Shrink for the section to Yes if any control in the section has these properties set to
- Picture (as background)
- Background color
- Controls in Design View
- Align controls across report
- Align controls vertically on report
- Line width - Resize all lines before trying to reduce the
- Line slanting - If a line prints jagged, the line is slanted.
- Control width/height - Size to show maximum data
- CanGrow/CanShrink: Allow for maximum size allowed by
the field's DataType by
using Can Grow/Can Shrink properties.
Errors in Expressions
When you use expressions to create calculated values, it is easy
to make mistakes. Typing is not as easy as it looks!
Common types of errors in expressions:
Omitted equals sign (=) : In a report or form control, an expression must
start with the equals sign (=).
Circular reference: The expression includes the
name of the control that the expression is in.
This usually happens when
you are putting an expression into an existing control that is named for a field
and your expression includes the same field. In this case,
you must rename the control.
Example: You drag the field
LastName to the report or form. Access
creates a control and names it LastName with
[LastName] as its control source. You edit the control source to
Error: This creates a circular reference because the
control's own name is still LastName.
Solution: Rename the control to something else.
Syntax: Occurs when your punctuation is not
right or you have omitted part of a function. It is easy to omit a parenthesis ( ) or a square bracket [
] or an ampersand & or a double-quote " or to leave off
one of the required arguments.
=[LastName]&", "[FirstName] Omits
=IIF([MiddleName] is Null) Omits
required two required arguments, what you want to see if True and if
False. Complication - If you only omit one argument for this function,
Access won't use Null for the other case.
Unknown function: Occurs when you mis-spell
the name of a function.
Unknown field: Access cannot find a field whose name is not
spelled correctly or which no longer exists!
Using property or object name for a
control/field: If you name a control something that Access also
uses for a property or an object, unexpected results may occur.
Example: You have a control or
field called Name, which is also the name of
a property for the Report and for each control. If you use [Name] in an
expression, you will see the name of the report or form instead of the
value you expected! Access is confused about which thing called
"Name" to use!
More on Errors
Step-by-Step: Create Report Manually
What you will learn:
|to create a report manually|
to create a calculated control
to handle a circular reference
worldtravel.mdb from the previous lesson
You are going to create a report that lists the clients
of World Travel Inc. There are not too many records yet, but you have to
Think first! What arrangement will work well for
the controls? Going across the page? Going down the page? Do you need all
fields from the source or just some of them? What is the purpose for the report?? What order will the user
need the data that you show?
In this case, World Travel Inc. wants to use the report
to see an overview of where their clients are so that they can target
their mailings and advertising better. You will only need the client's
name and address this time.
Create Blank Report
click on the Reports icon in the Database Window.
on the New button in the Database window's toolbar.
The New Report dialog opens with several choices.
on Design View.
Select the table Clients from the drop
Click on OK.
A blank report opens in Report Design View.
necessary, open the Field List by clicking the Field
List button on the
It shows the fields from the Clients table.
Save the report with the name Clients Report.
You can add controls to your report by dragging
them from the Field List or by using the Toolbox tools, just like you
could for a form.
the fields for the name and address in the Field List:
(Hint: Hold CTRL key down while clicking on each field.)
Drag and drop them on the Detail section.
to Print Preview to see how this looks.
There are 6 pages with a lot of wasted space.
single record takes a lot of space and is not easy to read in a column.
The records show in the order that they appear in the Clients table
Switch to Design View.
Create Calculated Text Value: FullName
It is more natural for people to read complete names and
addresses instead of individual fields. You can concatenate the text to
create those values. Recall that the ampersand character (&) is used to
glue together text values.
You will edit some controls to combine text values and
you will delete the rest of the labels and controls and move the controls
into a neat arrangement. This is like what
you would do to create mailing labels.
the label FirstName and edit it
to read Name.
The default value for a label is the name of the Control Source for the
matching data control. You can change a label without breaking anything.
the text box FirstName.
If necessary, open the Properties
You are going to change the Control Source to an expression that will
combine the name parts.
Change the value for the Name property to
Why change the name? To avoid a circular reference! You need to include FirstName
in the expression for the complete name in this same control.
Click in the Control Source property, which
still reads FirstName, and then on the
(with 3 dots) that appears.
Expression Builder dialog opens.
This dialog gives you point-and-click access to all of the existing
objects and functions in your database. It can really help when you are
not quite sure of the syntax of a function or the name of an object or
You don't really need these helpful features this time. The names of the
fields are easy to remember. The large text area just gives you room to
see what you are typing.
- Edit the text in the top section to read:
=[FirstName]&" "&[MiddleName]&" "&[LastName]&"
- The expression for a calculated control MUST start with the equals
- The ampersand is the glue between two text values.
- The text values can be
fields, like [FirstName] or literal text like " ", which produces a
single space in the resulting text. Also common for calculated names
is ", " which produces a comma and
- Click on OK to accept the new value for Control Source.
out of the Control Source property.
Now Access applies your expression to the control.
- Switch to Print Preview.
each record. How do the records look?
- Some names are cut off.
- You do not need the MiddleName,
LastName, and Suffix fields
anymore. It was smart to leave them in place until after
checking the width of the Name control. Sometimes it might not be
clear whether or not characters have been cut off unless you can
look at all of the fields.
Manage Name Fields
You can now get rid of several controls and labels and resize the
to Report Design View.
- Delete the controls and labels for MiddleName,
LastName, and Suffix.
- Delete the label for the Name
- Select the Name control.
- In the Properties dialog, change the
Width to 2".
(You could drag the right edge of the control to the right, using the
ruler to determine when the control is 2" wide.)
- Change the Can Grow property to
This will wrap the text onto another line if it won't fit.
The Clients table allows a total of 75
characters between the fields FirstName,
MiddleName, LastName, Suffix! By allowing the control to grow,
you are prepared for whatever the table will allow.
- Click out to accept the change.
Switch to Print Preview.
How do the records look?
- The longest current name shows completely!
- Extra white space where deleted controls were.
- Address control is too short
- Need to combine the other address fields in a calculated value.
Create Two Line Address
You will concatenate several fields to create the second line of an
When it is
obvious to the users what the data represents, you can omit the labels.
- Switch to Report Design View.
- Edit the Control Source for the City
control to concatenate the fields City, State,
Region and PostalCode.
=[City] & " " & [State] & " " & [Region] & " "
on the report's background to deselect the control.
There is a green triangle in the upper left corner of the control. This
means that there is a problem.
on the control again to select it.
A button appears.
- Hover over the button and a down arrow appears.
- Click the down arrow and a menu appears.
The problem is a circular reference in your
expression. You used the name of the current control in the expression
that defines the value for the control. This will not work!
- Click on .
- Change the name to CityState and
click out of the property to accept the change. The button
disappears. Neat, and very helpful, too!
- Delete the controls for State,
Region, and PostalCode.
labels for Address,
City, and Country.
- Select both the Address and City controls.
- Change the Width property to 2" wide.
(You can set both at the same time in the dialog. Dragging works
too since they were the same size to begin with.)
- Move the controls to new positions to match the illustration.
- Drag the bottom edge of the Detail section upwards,
to reduce the section to the smallest height possible.
Save the report. (Clients Report)
- Switch to Print Preview. How do the records look?
This report is not very easy to read. In the next lesson you can make
changes to add a title, group the records, and divide them from
each other more clearly.