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 ExpressionLet's look at each part of the expression:
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 ConcatenationPerhaps 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:
The complete expression is =[FirstName] & " " & [LastName]. |