Locked lesson.
About this lesson
Create a textbox to show the date and time a report was printed, page number, and total pages at the top of each page.
Exercise files
Download this lesson’s related exercise files.
Services_34_Start Page Header, Page Numbers, Date, Report Caption.accdb1.7 MB Services_34_Page Header, Page Numbers, Date, Report Caption.accdb
1.7 MB
Quick reference
Page Header, Page Numbers, Date & Time, Report Caption
Application Terminology
& Operator
Ampersand (&) combines, or concatenates, expressions together. Be sure to add space on either side of the & operator.
Line Tool
The Line Tool in the Controls group enables you to draw lines.
Rectangle Tool
The Rectangle Tool in the Controls group enables you to draw rectangles.
Select Controls Using Horizontal Ruler
When you click (or click and drag) in the horizontal ruler, every control that the imaginary vertical line (or rectangle) touches will become selected. This is a good way to find controls that are hanging past where you want a margin to be.
Report Caption
The Report Caption shows in the title bar of a report when it is displayed.
If the report is maximized, the Report Caption will display in the application title bar with the application name.
Steps
Add space to the end of a Caption
To add space to the end of a Caption, change the caption IN the control instead of on the Property Sheet. If you change the Caption property, Access will trim the value.
Best-Fit a Label
- Best-Fit a label by double-clicking one of the resizing handles. The big square in the upper left is not a resizing handle.
- Because controls are best-fit for a particular printer and the way they print varies, and screen fonts can also be different, make the label a little wider after you best-fit it.
Move a Label Independent of its Associated Control (and vice-verse)
- To move a label independent of its associated control (and vice-verse), drag it by the big square in the upper left.
Change Font Size in Zoom Box
- To change the font size in the Zoom Box, press
(Shift-F2) to open the Zoom Box.
- Click the Font command button in the lower right.
- The font will stay set as long as Access is open.
Change Font Size in Expression Builder
- To change the font size in the Expression Builder, press
(Ctrl-F2) to open the Expression Builder.
- Use Ctrl-Mouse wheel to make the font bigger and smaller.
Create Textbox to Show Date and Time Report was Printed, Page Number, and Total Pages at Top of Each Page
- To create a textbox to show the date and time a report was printed, page number, and total pages at the top of each page, go to the Design View of the report.
- Create a textbox in the Report Header section.
- Delete the associated label that gets created.
- Click in the Control Source property for the textbox.
- Press
(Shift-F2) to Zoom or
(Ctrl-F2) for the Builder.
- Type:
="Printed " & Now() & ", Page " & [Page] & " of " & [Pages]
- Now() is a built-in function that returns the current date and time. If you just want the date, you may use the Date() function instead.
- Page is the current page number.
- Pages is the total number of pages.
- Anything in quote marks will be printed literally (as it is). Be sure to balance the double quote marks, and include the text, space, and commas you need inside the double quote marks.
- Ampersand (&) combines, or concatenates, expressions together. Be sure to add space on either side of the & operator.
Show Vertical Lines Between Controls but Not Above
To create an effect like this:
- Set the Border Color of the controls
- Cover the top border with a rectangle or other control that has a white fill and no border.
- Draw a heavier Line below the controls.
- 00:04 This is Lesson 34 of Access 2013.
- 00:08 In this lesson, we will put the final touches on the report
- 00:12 we have been building in the last several lessons.
- 00:15 Hello, this is Crystal.
- 00:17 When you are done, your report should look something like this.
- 00:22 In the page header, we see the name of the report,
- 00:25 the date and time that the data was generated,
- 00:28 the page number, the total pages,
- 00:30 and labels for each column of the Detail section.
- 00:34 The Summary information shows that
- 00:36 12% in the time for 2015 was spent on Francis Fleming,
- 00:41 yet only 7% of the revenue was from his business.
- 00:46 The average hourly rate for Francis in 2015 is $26.67,
- 00:53 which is lower than the average rate
- 00:55 of $42.54 for all the jobs in 2015.
- 01:01 In 2015, 38% of the total time was spent
- 01:06 for 47% of the total revenue.
- 01:10 The overall average hourly rate is $34.22.
- 01:17 This business has only made $2,257.
- 01:22 The reason for the low number
- 01:24 is that not all the data is here yet.
- 01:27 In part 2 of this course, we will convert information
- 01:29 and get a more accurate picture of this business.
- 01:33 Double-click on the r_CustomerServicesByYear report
- 01:38 to open it in its Default View, which is now Print Preview.
- 01:43 The titlebar of the report shows what is in the report caption.
- 01:48 If the report does not have a caption defined,
- 01:51 the name of the report will be displayed.
- 01:54 Go to the Design View and set the Report Caption to
- 01:58 Customer Services By Year.
- 02:01 Save the Report and look at the Print Preview.
- 02:04 The report titlebar now displays the Report Caption
- 02:07 instead of the Report Name.
- 02:09 Select the Caption and copy it by pressing Ctrl-C.
- 02:13 You can also right-click
- 02:15 and choose Copy from the shortcut menu.
- 02:17 Maximize the report by double-clicking its titlbar.
- 02:22 To pick up tools, the ribbon needs to be expanded
- 02:25 so double-click on one of the ribbon tabs if it is not.
- 02:29 First we will add a report title to the Page Header.
- 02:32 Click on the Label control
- 02:34 in the Controls group of the DESIGN ribbon.
- 02:37 Click in the Page Header section above the column labels,
- 02:40 and paste the caption that was copied,
- 02:43 Customer Services By Year.
- 02:45 Move the control all the way up and to the left.
- 02:48 Make it Bold and change the Font Size to 12 points.
- 02:52 Use commands in the Text Formatting group
- 02:55 on the HOME ribbon.
- 02:57 Best-Fit the label by double-clicking one of the resizing handles.
- 03:01 Because controls are best-fit for a particular printer
- 03:05 and the way they print varies, make the label a little wider.
- 03:10 Access may show an error message
- 03:12 because this is an unassociated label.
- 03:15 You can choose to Ignore the Error.
- 03:18 Drag the right boundary to the left as far as it will go.
- 03:22 Obviously, something is sticking out.
- 03:24 Let's see what it is.
- 03:27 Just as you can click in the vertical ruler to select things,
- 03:30 you can also use the horizontal ruler.
- 03:33 Click just past the 7 inch mark.
- 03:37 The culprint is one of our lines.
- 03:39 The Width shows 7" so change the Left property to 0 (zero).
- 03:45 You should now be able to drag the right margin
- 03:48 back to 7 inches.
- 03:50 On the PAGE SETUP tab of the ribbon, choose Page Setup
- 03:54 and set the left report margin to 0.75".
- 03:59 I usually leave the right margin smaller in case
- 04:02 something goes over so that a blank page is not created.
- 04:07 On the Print Preview, since we are maximized,
- 04:10 the Report Caption is displayed in the titlebar
- 04:12 with the database caption.
- 04:15 In the Page Header, we see the label caption
- 04:17 we created with the report title.
- 04:20 Let's add the date, time, and page numbers.
- 04:24 Pick up the Textbox tool in the Controls group
- 04:27 and click on the right in the Page Header section.
- 04:30 Delete the new label and stretch out the textbox.
- 04:34 Change the Name.
- 04:35 Why is this textbox Unbound?
- 04:39 It is unbound because nothing is in the Control Source property.
- 04:44 Press Shift-F2 to Zoom the Control Source.
- 04:48 In the Zoom box, you may set the Font to a bigger size.
- 04:52 Type:
- 04:53 ="Printed " & Now() & ", Page " & Page & " of " & Pages
- 05:31 Now() is a built-in function
- 05:33 that returns the current date and time.
- 05:36 If you just want the date,
- 05:38 you may use the Date() function instead.
- 05:42 Page is the current page number.
- 05:44 Pages is the total number of pages.
- 05:47 Anything in quote marks will be printed as it is.
- 05:51 Ampersand (&) combines,
- 05:53 or concatenates, the expressions together.
- 05:56 Be sure to add space on either side of &.
- 06:00 Also be sure to balance the double quote marks,
- 06:04 and include the text, space, and comma you need inside them.
- 06:09 On the HOME ribbon,
- 06:10 click I in the Text Formatting group to make it Italics.
- 06:14 Also click Align Right in the Text Formatting group
- 06:18 to right-align the contents within the control.
- 06:22 Select one of the Continued label controls
- 06:24 and the textbox control with the date and page equation
- 06:28 and click Align Right on the QAT.
- 06:32 When you look at the Print Preview, you see there is an outline.
- 06:36 On the Format ribbon, set the Shape Outline to Transparent.
- 06:41 The report now shows this information without an outline.
- 06:46 There obviously is a fill and the textbox
- 06:48 is covering the top border of the column labels,
- 06:52 which is actually desireable, and we can use it to our benefit.
- 06:56 Because the service name was put into a group section,
- 07:00 there is more horizontal space to work with.
- 07:03 To make it easier to line things up,
- 07:05 set Left and Width properties to easy numbers.
- 07:10 My Left property for WsRate is currently 0.7083",
- 07:16 which I will change to 0.5 inches.
- 07:20 Shift-click the column label for Rate to add it to the selection,
- 07:24 and choose Align Left from the QAT.
- 07:27 The Left property of both of the controls is now 0.5 inches.
- 07:33 Align Left the rest of the label controls in the Page Header
- 07:36 and the textbox controls in the Detail section up to the date,
- 07:41 which we will make wider.
- 07:44 Although the date is fine for the format we are currently using,
- 07:48 you may want to use a format that takes more space.
- 07:52 Set the Width of the label and textbox for date to 1.1".
- 07:57 Collapse the Navigation Pane to get more space.
- 08:01 Perhaps your numbers may be bigger than the numbers here,
- 08:05 so make the Width of the Running Sum and its textbox 1.1".
- 08:11 Shift-click something that is already lined up on the right
- 08:15 and click Align Right on the QAT.
- 08:18 Also change the Width of the calculated amount
- 08:21 and its label to 1.1", and
- 08:24 right align them with the running sum controls using the QAT.
- 08:29 Look at the report to see where more space may be needed.
- 08:34 The Type column could be a little wider.
- 08:37 Use the QAT to Align Right the controls to the right of the Type.
- 08:43 The Width of the Type label is 0.7188" --
- 08:48 not sure how that happened.
- 08:51 The Width of the Type textbox is 0.7"
- 08:56 The Left Property is 2.8"
- 09:00 Drag the Type label out
- 09:02 to see about how much space there is left.
- 09:06 My drag did not go to an easy number.
- 09:09 The Left property of the WorkDate label is 3.6688",
- 09:14 which is not an easy number either, so this is why.
- 09:20 Set the Width of the Type label and textbox to 0.8"
- 09:25 The Left property of the WorkDate label
- 09:27 and textbox should, therefore, be
- 09:30 2.8 + 0.8 = 3.6"
- 09:36 Now that we have easy numbers for Left and Width,
- 09:40 use the QAT to line up the rest of the controls.
- 09:45 Use the scrollbar if you cannot see
- 09:47 the group footer calculations, which need to line up as well.
- 09:51 Line up the group calculations
- 09:53 based on CalcAmt under CalcAmt.
- 09:57 Scoot the averages over to the left so they are more centered.
- 10:02 Make the total labels narrower.
- 10:05 Line up the WsTime controls.
- 10:10 I see I forgot the calculated amount in the ServName footer,
- 10:13 so I'll select that and line it up with the CalcAmt textbox.
- 10:20 Scrolling up, since Customer is really big now,
- 10:23 we don't need to much space above it.
- 10:26 Select it, along with the Continued label,
- 10:28 and move both controls up by pressing Up-Arrow a few times.
- 10:34 Close the extra space below Customer
- 10:38 Set the Width of YearWork to 7" and center it.
- 10:38 by dragging the bottom border of the section.
- 10:43 Make the Customer control wider since there is extra space.
- 10:48 Save the report and look at Print Preview.
- 10:54 Close the Print Preview and look at the Report View.
- 10:58 I like the top border of the column labels getting covered.
- 11:03 Make the report title label a little taller and wider,
- 11:07 and set the Shape Fill to White.
- 11:10 If you don't have a control with text,
- 11:12 you could use white rectangle.
- 11:15 Stretch the textbox with date and page number
- 11:17 to the left till it overlaps the page title
- 11:20 so all the top borders of the labels are covered with white.
- 11:25 Copy one of the lines to the Page Header section
- 11:28 and line it up below the labels.
- 11:31 To make it easier to do that,
- 11:32 temporarily make the Page Header section a bit taller.
- 11:36 Paste the line into the page header and drag it down.
- 11:40 Then use the QAT to Align Top to one of the labels.
- 11:45 Select the new line in the Page Header section
- 11:48 and set Border Width to 2 points.
- 11:51 Close extra space in the Page Header.
- 11:55 To remove the vertical line before the Rate label,
- 11:58 set the outline to Transparent.
- 12:01 To give a little more space
- 12:03 between the labels and the vertical lines,
- 12:06 select each label control that is lined up on the right,
- 12:09 and put a space at the end.
- 12:12 Click in a label, press the End key, then type a space.
- 12:16 If you add the space on the property sheet,
- 12:19 Access will trim it.
- 12:21 Save and look at the Print Preview.
- 12:24 We are missing the closing parentheses in the summary labels.
- 12:28 Also, the Widths of the calculated amount controls
- 12:31 are not the same.
- 12:33 They all should be 1.1", and now they all are.
- 12:38 This report looks pretty good.
- 12:42 In the next lesson, we will build a main menu
- 12:44 to make it easier for users to pick options in the database.
Lesson notes are only available for subscribers.