- Learning How to Create Reports
- The Asia Database
- Begin the Report by Creating a Query
- Beginning a Report in Design View
- Detail Section
- Page Header and Footer
- Using Concatenation
- Report Header and Footer
- Creating a Report Using the Report Wizard
- Comparing the From-Scratch and From-Wizard Reports
- Conclusion
- Case Example
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using Concatenation
Chapter 7, "Find and Filter," introduced expressions and how to construct them. In Chapter 9, "Queries, Part II," you used the & operator in a parameter query but without explanation, which I promised to provide in this chapter. So here I review expressions and explain the & operator by answering this question: How does ="Page " & [Page] give you page numbers on every page?
Deconstructing the Expression
Let's look at each part of the expression:
- You start with the = sign. In the text box of a report, you use this to tell Access to get ready for an expression.
- Next is "Page ". When you viewed the page numbers in Print Preview (Page 1, Page 2, and so on), each began with Page. This is a text string that Access prints exactly as it's written. It's really just a label. When you create expressions, you put text strings in quotation marks, so it becomes "Page". But you want to have a space after Page; otherwise, pages would read Page1, Page2, and so on. So the full text string is "Page ", which has a space after the e.
- Now for [Page]. This denotes the Page property, which tells Access to print 1 on the first page and then add one to each succeeding page.
Overall, you have two parts to the expression: the text string Page with a space after it, and a property that returns a number on each page, starting with 1 and increasing by one on each succeeding page. How do you bring these two elements together to give you Page 1, Page 2, Page 3, and so on?
The answer is concatenation. The word is derived from the Latin concatenare, meaning "to link together." Because linking has its own specific meanings in Access, it's probably better to think of concatenation as a way to bind or tie together certain elements.
The symbol of concatenation is the ampersand (&). In the complete expression ="Page " & [Page], the & tells Access that, after it has printed Page and left a space, it should print the page number.
Bringing Fields Together with Concatenation
Perhaps the most important use of concatenation is tying fields together in a single control. I've emphasized that the way you store information has little to do with the way you ultimately want to use it. You therefore break down the data and store it in its smallest and purest form. This gives you maximum flexibility when you want to combine data in queries and reports.
People's names, for example, are usually stored in FirstName and LastName fields (there are exceptions). That makes sense because you'll often want to use either the first or last names separately (such as sorting people by family name).
But on numerous occasions you want first and last names to appear together in the normal way (as in Henrietta Maccabee or Jake Timberlane), as in an invoice. You could position the FirstName and LastName text boxes on the grid so that they are flush. But that's a sloppy and inelegant solution because the spacing between first and last names will inevitably be irregular.
The answer is concatenation, which lets you tie field values together so you can't tell that they are separate fields. Here is how to create the name in a report:
- Type = to signal that's it's an expression.
- Type [FirstName], the first name field.
- Type & to tie what comes before to what follows.
- Type " ". This is a single blank space, surrounded by quotation marks to indicate it's a text string.
- Type & to tie what comes before to what follows.
- Type [LastName] for the last name field.
The complete expression is =[FirstName] & " " & [LastName].