Page Numbers
By Richard Rost
2 years ago
Mastering Page Numbering in MS Access Reports
In this Microsoft Access tutorial, I'm going to teach you how to add page numbers to a report. We'll learn the basic "Page N", then "Page N of M", then I'll show you how to modify that so it's just "N/M". Then we'll learn how to hide the page numbering on the first page, which is good for a report header page, and I'll show you how to format it for double-sided printing where the odd pages are on the left and the even pages are on the right.
Blake from Frisco, Texas (a Platinum Member) asks: I'm trying to figure out how to get page numbering on my Microsoft Access reports so that it's compatible with two-sided printing, so the even page numbers are on the left, and the odd page numbers are on the right. Can you show me how to do that?
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, Access reports page numbering, format page numbers Access, hide first page number Access, conditional page numbering Access, modify page numbers in reports, double-sided report printing, even and odd page layout Access, report design view, Access page number alignment, Access report headers, concatenate text strings Access, page number equation Access, Access immediate if function, integer division Access.
Subscribe to Page Numbers
Get notifications when this page is updated
Intro In this video, I will show you how to add page numbers to your Microsoft Access reports. We will walk through adding basic page numbering, customizing formats like "page N" and "page N of M", and adjusting the text to your preferred style. I'll also cover how to hide the page number on the first page, which is useful for title pages, and show how to set up even and odd page numbering for double-sided printing. Along the way, you'll learn about modifying control sources and handling report layout settings.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
In today's video, I'm going to show you how to add page numbers to a Microsoft Access report. This is a beginner video, so I'm going to show you all the beginner stuff first, step by step.
We're going to do Page N, like page one, page two, page three. Then we'll do page N of M, so it's like page one of six. Then I'll show you how to modify that so it's just whatever you want to say, like 1/6, for example.
Then we'll do a little bit more advanced stuff. I'm going to show you how to hide the page numbers on the first page, which is good for a header, like a report header. Then I'll show you how to do even and odd pages so the odd pages are on one side and the even pages are on the other.
Today's question comes from Blake in Frisco, Texas, one of my Platinum members. Blake says, "I'm trying to figure out how to get page numbering on my Microsoft Access report so that it's compatible with two-sided printing so the even page numbers are on the left and the odd page numbers are on the right. Can you show me how to do that?"
Well, yes, certainly, Blake. I absolutely can, and in fact, you guys today have it a whole lot easier than we had back in the day. Back in the 90s when I first started working with Access we didn't have the options built in like you guys have, so I'm going to show you. It's super easy to do today.
Here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you want to. Let's make a real simple report.
So Create, and I like using Report Design. I don't like the wizards and the other stuff in here, except maybe the label wizard. Let's go to Report Design.
Here's our blank report. I'm going to double-click right there, and I'm going to set the record source to wherever I'm getting my records from. This can be a table or query. I'm just going to pick the customer table.
Okay, then we're going to go to Report Design and Add Existing Fields. Pick what fields you want to add. Let's go CustomerID. I'm going to hold the control key down: FirstName, LastName, and how about Notes.
Okay, then let that control key go, click and drag, drop them right about there, and those are the fields that I want on my report. We'll slide a little bit, come down here, grab the bottom of that detail section, and slide it up.
That's what we got so far. I'm going to save it, Control+S. We'll say this is my customer report. I can close that field list now and let's take a peek at what it's going to look like. Right click on the title bar here and then go to Print Preview.
There we go. Now, the print preview never takes this ribbon into account, so I always close that ribbon. You can see what it looks like. One, two, three, up to five, whatever page.
On the bottom down there, I want to put some page numbering. Let's right click, go back to Design View, and under Report Design, you're going to find Page Numbers right there. Click on that, you've got a bunch of options.
Here's Page N, which is just like page one. Page N of M, which is page one of six. Where do you want it, top or bottom of the page? I like the bottom myself; some people put them on the top, whatever.
How about the alignment? Where do you want it to be aligned? There's Left, Center, or Right. We'll talk about Inside and Outside in just a minute. I'm going to pick Left right now. Here's the option if you want to show the number on the first page or not.
If you turn that off, it won't show on the first page, which is nice if you have a report header, like a title page or something. I'll hit OK. There it is; it drops a little text box down here.
Let's take a look at what's inside that text box. That's one thing, to know how to do this kind of stuff, but if you really want to learn Access it's good to know why it's doing it--what's exactly in there.
Let's double-click on this guy, bring up the property sheet, and right here we can see it says Control Source. I'm going to click right there and then hit Shift+F2 to zoom in. Let's just zoom in on that little box.
So what's inside here? This is basically an equation. It starts off with the equal sign and then we've got "Page" inside of quotes. Whenever you see something inside of quotes that is literal text, so it's going to put the word "Page" in there followed by a space.
Then, this little guy, that's the concatenation operator; that puts two text strings together. If you want to learn more about concatenation, I got a whole separate video on my website. I'll put a link down below.
That's going to add on to that. After that, it's going to put "Page." Now, the [Page] here is inside square brackets. That says it's either a field or a special name, like a special variable name. Access sees that and it's going to put whatever page number we're on, on the current page, in that spot. So you're going to have Page 1, Page 2, and so on.
Then we got more concatenation, add more stuff, then we got "of" inside of quotes. That's the actual literal "of" with spaces around it. Then, you got [Pages]. [Pages] is the total number of pages in this entire report.
This whole thing gets put together and gets put inside this text box which is called Text4. I'm not a big fan of leaving it Text4. I usually try to rename these things, like PageNumText or whatever you want to call it. Normally, I only bother renaming those if I'm going to refer to them from something else, but you can leave it Text4 if you want to. It's not a big deal.
Now if I save it (Control+S) and I right click Print Preview, down here, again, I got to close the ribbon. That's one of my pet peeves. I was ever keeping my list of stuff for the Access dev team, that's one of my pet peeves--you have to hide the ribbon all the time.
Come down here. There we go, page one. If I go to the next page, this is page two, three, four, five, and so on. Pretty cool stuff.
If you want to modify this at all, you don't want it to have the word "Page" there, all you have to do is come in here in Design View. Once this text box is built for you, just come in here and change it however you want it. Let's say you want this to be just like 1/6, get rid of the word "Page." You just want the number followed by a slash, maybe keep those spaces there, then the total number of pages.
Let's try that, see what that looks like. Save it, right click, Print Preview, hide the ribbon, and there you go. There's 1 of 6, then 2 of 6, 3 of 6, 4 of 6, and so on. However you want that to look, whatever you want it to look like.
Of course, you can come in here and you can change the font and you can make it bigger, smaller, or whatever. Lots of different things you can do, but that's how you can manipulate what's inside there.
Let's take a look at what happens if we pick that option to hide the page numbers on the first page. Let me delete that, come back up to Report Design, Page Numbers, and let's turn off "Show Number on First Page."
It's a little bit different now. Let's come in here, click on the Control Source, and open it up. Notice it adds this word IIF there. IIF stands for Immediate If. That's basically an "if then" statement as part of a function.
Again, I got a whole separate video on the IIF function if you want to learn more about it, but essentially it's saying if the page, if the current page number, is greater than one, then you're going to put this whole thing in there, which is the page number we had before. Otherwise, with the little comma, otherwise--this is if it's false, in other words if page number is equal to one--it puts in an empty string. Quote quote is an empty string.
Now what happens is if you Print Preview, you'll see the page one is missing the page number. There it is on page two and three and so on, and again, that's good if you're putting like a report header on.
You can add a report header so page one is like a title page or something different. I got whole separate videos on how to do that.
Last bit, let's answer Blake's question. Let's delete this guy. How do we do that even/odd page stuff? That's what this last option is for. Page Numbers, right down here under Alignment, there's Inside or Outside and I can never remember which one is which. One of them puts the odd numbers on the left and the other one puts the odd numbers on the right. I think we want Inside for this one.
We'll put that back on, hit OK, and you can see there's two of them now. If you open this up, it says "If [Page] Mod 2 = 1" -- what is Mod? Mod is modulo or modulus. It's the remainder after an integer division. Basically, it can tell us something is odd or even. Again, I got a whole separate video on this, I'll put a link down below to it.
This says if it's odd, if Mod 2 is 1, that means if it's odd, do this, otherwise put blank. The other text box over here has the opposite: if Mod 2 equals 0, which means it's even.
Now if we save it, right click, Print Preview, hide the ribbon, you can see there's page one on this side. Over here, now there's page two. You can see page two is right there, it's kind of halfway across the page. It's kind of hard to see, let me zoom in. You're not getting a full page. That's because our report width is smaller. That's one of the defaults. Again, this is one of my pet peeves.
When they build this report for you with the basic report, it only goes out to about six and a little bit more inches. If your default page setup is an 8.5 by 11 sheet of paper and your margins are usually half inches, if you come over here to Margins, you got Narrow, so you got half an inch--quarter inch on each side.
You can technically come in here and you could bring this out to right about there's the edge of the page, right, 8.5 inches. Right there would be your half inch margins, quarter inch on each side. I like to bring it in just a little bit more because some printers will go over the edge of that. You'll get warnings that your report width is too wide, etc.
Now we could take this and slide it over here, but we're not done yet. Watch--save--this is another email I get all the time. Right click, Print Preview, turn off the ribbon. It's still not quite all the way over there, see it? It's kind of hanging out right there. Why is that?
I'm going to go back into Design View. The reason why is because this label is left aligned, so the text is over here. All you got to do is go into Format and then right align that. That's another note for the Access dev team--you could fix that one too.
Now save it, right click, Print Preview, and turn off the ribbon and there we go; now it's over here in the corner where it belongs. You got to fix your margins, your page width, and then you got to put the label or the text box where it belongs.
I cover a lot more with reports and page numbers in a couple of my classes. I start talking about it in Access Beginner 9. Then we do more in Access Expert Level 5, and more in Level 12 including sorting and grouping levels because you can reset that page number every group too. Lots of different tricks you can do.
A lot of people ask me why my classes are set up like this. Why don't I cover everything about reports in one level? I prefer what's called a breadth-first training method, as opposed to depth-first. Depth-first would be okay, we're going to learn everything about tables today, then the next class everything about queries. That's how a lot of books are written.
But me personally, I think a breadth-first way to learn is better. So, we'll learn a little bit about tables, then a little bit about queries, then a little bit about forms, then a little bit about reports, and that's how I teach--I'll give you a little bit of this, then a little bit of that, and it kind of meshes together.
So there isn't one report lesson. If you want that, then get a textbook. Textbooks are usually written that way. There is an index here on my website, though. If you want to learn everything about, let's say, page numbers, you can come to the index and search for page number and it will show you all the lessons that cover page number.
Each one of them has slightly different techniques.
But there you go. That is your TechHelp video for today. I hope you learned something. Now you know pretty much all there is to know about page numbers.
There is more page numbering stuff, but then we start getting into developer level stuff, using some VBA code to tweak the page numbers. There are all kinds of cool things you can do, folks.
But that's it for today. Live long and prosper my friends. I'll see you next time.Quiz Q1. What is the primary purpose of adding page numbers to a Microsoft Access report?
A. To indicate the current and total number of pages for navigation and referencing
B. To increase the size of the report file
C. To make the report open faster
D. To change the report's data source
Q2. In Microsoft Access reports, what does the expression [Page] represent?
A. The total number of pages in the report
B. The current page number being displayed or printed
C. The primary key of the table
D. The name of the report
Q3. Which section of the report do you typically add a page number to?
A. The report header
B. The page header or footer
C. The detail section only
D. The data source section
Q4. What does the expression [Pages] indicate in a report's text box control source?
A. The page number of the first page only
B. The total number of pages in the entire report
C. The number of records on the page
D. The print margin size
Q5. When constructing custom page numbering, why might you use concatenation (e.g., using the & operator) in the Control Source?
A. To add new fields from the table
B. To combine literal text and page numbers into a single string
C. To create new queries
D. To filter data from the report
Q6. What effect does turning off "Show Number on First Page" in the Page Numbers dialog have?
A. Hides the report header
B. Page numbers are not displayed on the report's first page
C. Page numbers are shown in red font
D. The report will not print
Q7. What does the IIF function accomplish within a page number expression?
A. Imports data from Excel
B. Conditionally displays page numbering based on the page number
C. Automatically sorts the report
D. Changes the report orientation
Q8. What does [Page] Mod 2 = 1 check for when creating even and odd page alignment?
A. Checks if the report has only one page
B. Checks if the page number is odd
C. Checks if the page number is even
D. Checks if the page is a header
Q9. In the context of alignment choices for page numbers in Access, what is the difference between "Inside" and "Outside" alignment?
A. "Inside" centers all page numbers; "Outside" hides them
B. "Inside" places page numbers closer to the binding edge; "Outside" alternates them between left and right for facing pages
C. "Inside" deletes all page numbers; "Outside" prints them in italics
D. "Inside" and "Outside" are only for headers, not footers
Q10. Why might adjusting report margins and width be necessary when working with page numbers in Access?
A. To ensure that page numbers appear within the printable area and are correctly aligned
B. To hide the report title
C. To make the font larger
D. To decrease report printing speed
Q11. According to the video, why might you want to rename a text box from its default name (e.g., Text4) to something else like PageNumText?
A. It is required by Access for the report to work
B. To more easily reference the text box elsewhere, especially if you use it in other expressions or code
C. To increase report speed
D. To change the page numbering format
Q12. What training method does Richard mention he prefers when teaching Access?
A. Depth-first, focusing on one subject at a time completely
B. Breadth-first, covering a little of each subject and building complexity over time
C. Only covering forms in each lesson
D. Teaching everything about VBA first
Q13. What is a common use case for hiding the page number on the first page of an Access report?
A. When the first page serves as a cover or title page
B. When the report has less than two pages
C. When there are no headers in the report
D. To always display the current date
Q14. When setting up even and odd page numbers for two-sided printing, which tool/function is primarily utilized?
A. The SUM function
B. The MOD operator within an IIF statement
C. The FORMAT function
D. The COUNT function
Q15. If your page number text box is not fully aligned to the edge of the page, what is a likely cause?
A. The font size is too large
B. The text box alignment (e.g., left, right) and margins/page width settings need to be adjusted
C. The report footer is missing
D. The data source is incorrect
Q16. Why does Richard prefer not to use the report wizards (except maybe the label wizard) when creating reports in Access?
A. He wants to save time by using less flexible tools
B. He prefers more control and customization with Report Design view
C. Wizards are not available in Access
D. Wizards are mandatory for adding page numbers
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-A; 11-B; 12-B; 13-A; 14-B; 15-B; 16-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on how to add page numbers to a Microsoft Access report. This lesson is geared towards beginners, so I will start from the basics and walk you through each step.
The simplest way to add page numbers is to set up your report to display just the page number, such as page one, page two, and so on. From there, you can expand this feature to show a format like page one of six, so the report reads "Page 1 of 6." I will also explain how to make further adjustments, like displaying the page numbers as "1 6" or any other style you prefer.
After covering these fundamental options, I will demonstrate some intermediate techniques. These include hiding the page number on the report's first page, which is helpful if you have a title or cover page. I will also show you how to set up the page numbers so they appear on alternating sides of the page, which is ideal for double-sided printing. This way, odd page numbers can appear on one side and even numbers on the other, matching traditional book layouts.
A common question concerns how to number pages in a way that is compatible with double-sided printing. It is much easier to do this now with the improvements Microsoft has made in Access over the years. When I first started with Access back in the 1990s, we did not have these built-in options, so it is nice to see how much more straightforward the process has become.
To begin, I set up a simple report in my free TechHelp template database. After creating a new report in design view, I selected my data source - in this example, I used the customer table. I added a few fields, such as CustomerID, FirstName, LastName, and Notes, and arranged them onto the report layout as I liked.
Once I had the basic structure in place, I previewed the report to see my data. Then, I added page numbers by going back to the design view and choosing the Page Numbers option from the Report Design Tools. This option provides several choices. You can select to display just the current page ("Page N") or the current page and the total page count ("Page N of M"). You get to choose whether the numbers appear at the top or bottom of the page, and you can set the alignment to the left, center, right, inside, or outside.
Alignment options like "Inside" and "Outside" are useful for setting up two-sided printing, with odd and even page numbers appearing in different places. For now, I selected a simple setup: page numbers at the bottom left of the page.
After placing the page number, it is worthwhile to look at the control's properties to understand how it works. The control source for the page number is a formula that strings together literal text with the actual current page number ([Page]) and the total number of pages ([Pages]). The quotation marks indicate literal text, square brackets are for fields or special values, and the ampersand is used to concatenate, or join, different text items together. If you want to adjust the caption or change the format to something like "1 6," simply edit this control source formula as you prefer.
You can also customize the formatting, such as the font or size, directly in the report design view.
Sometimes, it is useful to hide the page number on the first page - for example, if you have a title page. This option is available in the Page Numbers dialog. When you choose not to show the number on the first page, the formula inside the control changes. It uses the IIF function to check the current page. If the page number is greater than one, it displays the number; otherwise, it returns an empty string so that nothing is shown on the first page. I have full tutorials on how the IIF function works in Access if you would like more details.
For reports intended for double-sided printing, you can use the "Inside" and "Outside" alignment options to place odd page numbers on one side and even numbers on the opposite side. The formulas behind these controls use the Mod function to determine if the current page number is odd or even, then place the number in the appropriate spot. For instance, [Page] Mod 2 = 1 will identify odd-numbered pages.
When adjusting page numbers and their positioning, make sure your report's width matches your paper size and margin settings. This helps prevent positioning problems, such as numbers spilling into the unprintable area. If you notice numbers are not perfectly aligned, set the text alignment (such as right-justified for numbers on the right) to ensure everything appears correctly when printed.
If you are interested in additional report and page numbering techniques, I cover these topics in depth in my classes, starting with Access Beginner 9 and further in Access Expert Levels 5 and 12. There, you will find discussions about sorting, grouping, and resetting page numbers for each group.
I organize my courses using a breadth-first approach. This means I introduce a little bit of each topic, moving through tables, queries, forms, and reports, rather than focusing entirely on one area at a time. If you prefer the depth-first method, where everything about reports is grouped together in one lesson, textbooks often follow that structure. For easy reference, there is an index on my website where you can search for specific topics like page numbers to see all the relevant lessons.
That wraps up today's lesson about adding and customizing page numbers in Microsoft Access reports. There are even more advanced tricks you can use with VBA, but those are better suited for developer-level tutorials.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Adding basic page numbers to Access reports
Using "Page N" numbering in reports
Using "Page N of M" numbering in reports
Customizing page number display text
Changing font and size of page numbers
Hiding page numbers on the first page
Understanding and editing page number control source
Using concatenation in report controls
Explanation of [Page] and [Pages] keywords
Using the IIF function for conditional page numbers
Adjusting report margins and page setup for correct positioning
Aligning page numbers for duplex printing
Setting page number alignment to Left, Center, Right, Inside, or Outside
Placing even and odd page numbers on separate sides for booklet printing
Using Mod function to determine odd and even pages
Adjusting report and text box width for proper page number placement
Setting text alignment (left or right) in page number controls |
