Forms & Reports:
Create Report Manually

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

You have much more control over your report if you create it yourself. You can arrange controls in a logical manner that still saves space. You should be able to get all of a record's controls onto one printed page. You can group the records in useful ways. You can control exactly how it prints.

The Report Design View is almost identical to Form Design View. The only new feature is Sorting and Grouping.

 Report Design View

Default report
includes Page Header and Footer sections

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/LinkTo subtopics
    Designing FormsTo subtopics
    Designing Reports Arrow: subtopic open
    Icon: StepReport Wizard
         Printing Reports
    Icon: StepCreate Report Manually
    Icon: StepGroups
    Icon: StepTotals - Simple
    Icon: StepTotals - Calculated
    Icon: StepSharing Reports
    Special Forms & ReportsTo subtopics
    ExercisesTo subtopics


Report Controls

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.

Common adjustments:

  • 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 Yes.
    • 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 report's width.
    • Line slanting - If a line prints jagged, the line is slanted.
    • Control width/height - Size to show maximum data expected.
    • 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 (=).

    • Example: You can use =[Price]*0.0925 for the control source for a control in a form or report. Confusingly, [Price]*.0925  or SalesTax:[Price]*.0925  is correct for a query.

  • 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
       =[LastName]&", "&[FirstName].
      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.

    • Example: =[LastName]&", "[FirstName] Omits second &.

    • Example: =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.

    • Example: Typing IFF instead of IIF for a conditional expression.

  • 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!

      Correct report  Wrong: Expression using Name

More on Errors

Icon: Step-by-Step 

Step-by-Step: Create Report Manually

 Icon: Step-by-Step

What you will learn:

to create a report manually
to create a calculated control
to handle a circular reference

Start with:  Class diskresource files, 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 start somewhere!

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

  1. Database Window: Reports - New buttonIf necessary, click on the Reports icon in the Database Window.

  2. Click on the New button in the Database window's toolbar.
    The New Report dialog opens with several choices.

  3. Dialog: New Report - choose Design view and ClientsClick on Design View.

  4. Select the table Clients from the drop list.

  5. Click on OK.
    A blank report opens in Report Design View.

  6. Report Design View: Blank database; Field List for ClientsIf necessary, open the Field List by clicking the Field List button Button: Field List on the toolbar.
    It shows the fields from the Clients table.

  7. Icon: Save Save the report with the name Clients Report.

Add Controls

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.

  1. Report Design View: drag from the field listSelect the fields for the name and address in the Field List:
    FirstName, MiddleName, LastName, Suffix
    , City, State, Region, PostalCode, Country
    (Hint: Hold CTRL key down while clicking on each field.)

  2. Drag and drop them on the Detail section.

  3. Report Print Preview: Clients - 6 pages, single narrow columnSwitch to Print Preview to see how this looks.
    There are 6 pages with a lot of wasted space.
    Report Print Preview: single record from reportA 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

  4. 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.

  1. Report Design View: Label changed to "Name"Select 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.

  2. Dialog: Properties - Control SourceSelect the text box FirstName.

  3. If necessary, open the Properties dialog.
    You are going to change the Control Source to an expression that will combine the name parts.

  4. Change the value for the Name property to FullName.
    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.

  5. Click in the Control Source property, which still reads FirstName, and then on the ellipsis button Button: Ellipsis (3 dots) (with 3 dots) that appears.

    Expression BuilderThe 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 control.
    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.

  6. Edit the text in the top section to read:
    =[FirstName]&" "&[MiddleName]&" "&[LastName]&" "&[Suffix]
    • The expression for a calculated control MUST start with the equals sign (=).
    • 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 a space.
  7. Click on OK to accept the new value for Control Source.
  8. Report Design View: Clients Report with calculated field for a complete nameClick out of the Control Source property.
    Now Access applies your expression to the control.

  9. Switch to Print Preview.
  10. Print Preview: Name is cut offInspect 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 calculated control.

  1. Report Design View: after formatting calculated controlSwitch to Report Design View.
  2. Delete the controls and labels for MiddleName, LastName, and Suffix.
  3. Delete the label for the Name control.
  4. Select the Name control.
  5. 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.)
  6. Change the Can Grow property to Yes.
    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.
  7. Click out to accept the change.
  8. Report Print Preview: last record after formatting/deletingSwitch 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 address. When it is obvious to the users what the data represents, you can omit the labels.

  1. Switch to Report Design View.
  2. Edit the Control Source for the City control to concatenate the fields City, State, Region and PostalCode.
    =[City] & " " & [State] & " " & [Region] & " " & [PostalCode]
  3. Control with green triangle at upper left.Click on the report's background to deselect the control.
    Surprise! There is a green triangle in the upper left corner of the control. This means that there is a problem.
  4. Report Design View: popup tag = Circular ReferenceClick on the control again to select it.
    A button appears.
  5. Hover over the button and a down arrow appears.
  6. 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!
  7. Click on  Edit Control Name .
  8. Change the name to CityState and click out of the property to accept the change. The button disappears. Neat, and very helpful, too!
  9. Delete the controls for State, Region, and PostalCode.
  10. Report Design View: two calculated controlsDelete the labels for Address, City, and Country.
  11. Select both the Address and City controls.
  12. 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.)
  13. Move the controls to new positions to match the illustration.
  14. Drag the bottom edge of the Detail section upwards, to reduce the section to the smallest height possible.
  15. Icon: Save Save the report. (Clients Report)
  16. Switch to Print Preview. How do the records look?

Print Preview: In one column

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.