Concatenation is always explained in a footnote, or as a section of something else. It is rarely explained as such, as a concept. I was making exactly that mistake in another article, when the section started to outgrow the core topic... I still needed some time to realise that concatenation is a topic
This article is specifically written for expressions on reports (and forms). Everything said here holds true for queries (the syntax is slightly different) but not everything for Visual Basic. In particular, the handling of quotes and newlines is very different.
Forms are normally meant for editing, and concatenated results are not editable. Although concatenation has its place on forms, this article will use 'reports' instead of 'forms and reports' in most places.
It should prove useful to anyone who needs to build text from text elements in reports (or forms), and it might even hold one or two surprises for experienced Access developers.
The examples and suggestions aren't version-specific.
What is Concatenation?
The word is used in computer languages to describe the process of building text from smaller elements. For example, the name "Prof. Abraham T. Callahan Jr." is likely to be stored in a database in several different fields: first name, initial, last name, suffix, title... When the full name is required, the individual fields need to be assembled again, not forgetting the spacing and perhaps the punctuation. Also, when a report shows "Mean acceleration: 23 m/s²", the actual data is probably just the '23', the rest is text added around the value for readability.
This operation is called concatenating
text elements and field values, and the symbol used to do so is a concatenation operator
In the expression "2 + 2", the symbol '+' is the operator. It indicates what should be done with the numbers on each side. The expression "'A' & 'B'" says that the characters, or strings
of characters, on each side should be simply stuck together as a new string
: 'AB'. The symbol '&' is the main concatenation operator.
In other words, the expression "2 + 2" will evaluate to '4', while "2 & 2" will return '22', a concatenation instead of an addition.
When the operator '+' is used with numbers (or anything stored numerically, like dates), it means 'addition'. When both sides are characters, however, the operator will act as a secondary concatenation operator: "'A' + 'B'" also returns 'AB'. Technically, this behaviour is called "operator overloading", but this isn't as interesting as the way '+' can be used to greatly simplify expressions when Null is involved (see below).
Concatenation also occurs in formatting. The number 1240.5 could become "$1,240.50" or "EUR 1'240,50" when formatted as "currency". The dollar symbol or the currency code have been concatenated with the text representation of the number (including thousand separators).
Our fundamental tools for this article are thus the concatenation operators and formatting (either as the "format" property or through the Format() function). Two additional functions are also mentioned, Nz() and IIf(), not because they perform concatenation, but because they are often used in this context.
When a text box (or a combo box) is added to a form or report, it often comes with a label. This is already a form of concatenation: the reader will see for example:
Age: 42 yrs
On forms, the label is often formatted differently, for example to make it obvious that it cannot be edited, while the number can be. On reports, the printed result is almost always undistinguishable from an equivalent concatenation of "Age: " and the formatted field value.
As such, labels are the simplest and most common concatenations found in applications. They deserve mention, but we have nothing special to say about them here.
As said earlier, formatting performs concatenation. The examples below are all valid entries for the format property of a field or control:
"gain: +"0%;"loss: -"0%;"stable..."
As can be seen, the format setting can contain text. It is normally quoted using double-quotes, or simply escaped
(preceded with a backslash) when it's a single character. Note that the percent sign isn't quoted, because it performs another task: multiplying the value by 100 before display.
The last example also shows conditional formatting: different text is used whether the value is positive, negative, or zero. The same result can be achieved by nested calls to IIf() (or by the Switch() function), but formatting is much easier.
The fourth section of formatting is used when the value is not a number, meaning when the value is Null:
This works also for strings, the sections used are: (1) for data, (2) for Empty or zero length strings -- ZLS, (3) Null values. The following is a sample demonstration format:
This usage of the text format is especially useful on forms, to highlight which fields are required. The message will only be visible while the field is Null:
@;;[Blue]"-- enter your name --"
The added colour code is a nice touch, but it does not survive concatenation, so colours will not be used again in this article. In Reports, this can be used to signal missing data explicitly:
In the rest of the article, formatting is used through the Format() function. The formatting codes are the same, they only need to be quoted entire in single quotes. The last example could thus become:
= "Favourite colour: " & Format([MyColour], '@;;"(not documented)"')
Since "format" requires double quotes, it makes sense to use preferably single quotes consistently in form or report expressions, even when no strings within strings
are needed. When it isn't possible to use single quotes, e.g. in Visual Basic, the quotes within quotes need to be doubled again, creating double-double-quotes:
strText = "Colour: " & Format(Me.MyColour, "@;;""
The underlined double-double-quotes will become regular double-quotes when passed to the Format() function, and work as expected. It is a little harder to read, but sometimes necessary. Luckily, in report controls, it is always possible to avoid this awkward syntax.
The basic operator is the ampersand, used to "assemble" the contents of fields and quoted constants.
= [FirstName] & ' ' & [LastName]
The square brackets are added automatically if your field names do not contain spaces or other troublesome characters. [FirstName] is either a field or another control on the report -- this becomes useful for calculations rather than for concatenation.
The space is quoted with single quotes. Double quotes are also allowed, if used consistently. This makes it possible to quote strings within strings
using the other notation. As said earlier, my preference is to use single quotes, for various reasons.
A slightly more complex example:
= UCase([LastName]) & ', ' & [FirstName] & ' ' & [Initial] & '. (' & [Title] & ')'
This shows as "DOE, John A. (Dr)". This is nice unless there is no middle initial or no title: "DOE, John . ()". If needed, Null values can be converted using Nz(). For example, display "nmi" for "no middle initial" or "?" for missing title:
... & Nz([Initial], 'nmi') & ...
... & Nz([Title], '?') & ...
Now you would see "DOE, John, nmi. (?)". Not necessarily nicer in this case. The reverse operation, removing typographical elements when a field is Null, if often done with a rather complex syntax involving IIf(), for example:
... & IIf([Title] Is Null, Null, ' (' & [Title] & ')')
If the title is Null, do nothing, else show the title with surrounding brackets and a leading space.
Luckily, there is a shorthand notation for this, using '+' instead of '&' as operator.
... & ' ('+[Title]+')'
Technically, '+' propagates
Null, in effect erasing the surrounding constant text up to the next '&' (and the '+' has operator precedence
over '&', so it is always evaluated first). Rewriting our full name expression, it becomes:
& ', '+[FirstName]
& ' '+[Initial]+'.'
& ' ('+[Title]+')'
The underlining shows what "lives or dies together". Each segment is evaluated individually (and can become Null) before building the full name. Our John Doe is now "DOE, John", and you could also find a "JECKYL (Dr)", without the comma.
Mixing the operators '&' and '+' for string concatenation is the most useful of all concatenation tricks. It is specific to Jet and Visual Basic, and I really miss it in many other languages that have only one concatenation operator. However, '+' obviously can't be used to concatenate numeric values, at least not directly.
The implicit conversion to a number loses the field's formatting. For example:
= [ProductName] & ': ' & [UnitPrice]
= 'discount: ' & [Discount]
Would display for example "Guaraná Fantástica: 4.5" or "discount: 0.05". You need to format your numbers explicitly:
= [ProductName] & ': ' & Format([UnitPrice], 'Currency')
= 'discount: ' & Format([Discount], 'Percent')
= 'discount: ' & Format([Discount], '0.0%')
The same is obviously true for calculations:
= 'Total: ' & Format([UnitPrice] * [Quantity] * (1 - [Discount]), 'Currency')
Formatting numbers avoids a pitfall: trying to use '+' for concatenation. If used with a number on both sides, it means 'addition', and with mixed elements, you get an #Error. The number needs to be converted to text first, or Format() can be used instead to add text around it:
= 'Size: ' & [Size] + 'm' -- #Error
= 'Size: ' & Trim([Size]) + 'm'
= 'Size: ' & Format([Size], '0.00\m')
The first example doesn't work, as expected. It is possible to convert the number to a string using the functions Str() or Trim() -- which both return Null when the argument is Null -- or to format the number. You will see "Size: " when the field is Null or "Size: 1.78m", for example, when it isn't. The expression with Trim() could also display "Size: 1.4m" (it doesn't specify a number of decimals).
='Temperature: ' & Format([Temp],'0" °C"') & Format([Temp]*9/5+32,'" ("0" °F)"')
The same field is formatted twice, once directly as degrees Celsius (as stored in the database) and a second time using a conversion expression. Both Format() functions will return nothing when the field is Null, resulting in: "Temperature: ". If it isn't, you could see "Temperature: 20 °C (68 °F)"
Also remember that the fourth section of number formats is used when the field is Null. The following are identical:
= IIf([ReorderLevel] Is Null, "Not reordered", "Reorder Level: " & [ReorderLevel])
= Nz("Reorder Level:" + Str([ReorderLevel]), "Not Reordered")
= Format([ReorderLevel], '"Reorder Level: "0;;;"Not reordered"')
ReorderLevel -- no expression, format: "Reorder Level: "0;;;"Not reordered"
If the reorder level is Null, display
"Not Reordered", else display the number with the label
"Reorder Level:" and a space.
All four solutions display the same result.
Format() can thus replaces Nz() and any concatenation trick using '+' for numbers, and I suggest trying it first when dealing with numbers.
In situations where controls can grow and shrink, it is often simpler to use stacked text boxes to "concatenate vertically". When that is not possible, on forms or when printing labels, we need to learn how to insert newlines between field values.
As an example, let's take company addresses, where "contact name", "contact title", "address", and "P.O. Box" can all be Null. We don't want an empty line when that happens, we want the address block to be compact.
= [CompanyName] & '
Attn: ' + [ContactName] & '
(' + [ContactTitle] + ')' & '
' + [Address] & '
' + [POBox] & '
' + ([Region]+'-' & [PostalCode]+' ' & [City]) & '
' + UCase([Country])
This example is perhaps a little frightening at first -- it is a complex example -- but it gets quite readable after a while. A newline is inserted between quotes: open a quote, press Ctrl+Enter, and close the quote. It will be inserted just like anything else you type between quotes (and the same rules regarding '&' versus '+' are applied). Perhaps I should have started with something simple:
= [ContactName] & '
(' + [ContactTitle] + ')'
= 'Total: ' & Format([Total], 'Currency') & '
In the first example, the second line and the brackets are only added when there is a "contact title". In the second, the indication in the second line is always present, just like the caption "total".
Note: If you show this to a real
programmer, expect one of two reactions: the sad condescending face and "sorry, dear, you can't to that!" or two eyes growing very large, followed by a sprint to the next keyboard to see if that
Normally, you can't quote newlines in computer languages. It's practically a taboo. Jet SQL and the expression evaluator of forms and reports is quite the exception in that regard, but it's much more readable than what you "should" have typed:
= [CompanyName] & Chr(13) + Chr(10) + 'Attn: ' + [ContactName] & Chr(13) + Chr(10) + '(' + [ContactTitle] + ')' & Chr(13) + Chr(10) + [Address] & Chr(13) + Chr(10) + [POBox] & Chr(13) + Chr(10) + ([Region] + '-' & [PostalCode] + ' ' & [City]) & Chr(13) + Chr(10) + UCase([Country])
Programmers read this fluently, because they know character thirteen's and ten's nicknames -- CR and LF -- as well as their full names -- ASCII Carriage Return and ASCII Line Feed -- but normal users do not read the last expression too well. Use the quoted new-lines instead.
Even more surprising, perhaps, you can use newline in a formatting string...
= 'Units in stock: ' & [UnitsInStock] & Format([ReorderLevel], '
"(reorder level: "0")"')
The second line, e.g. "(reorder level: 12)" will not appear at all when the field is Null.
Multi-line expressions cannot be edited easily in the property sheet, or when the control is too small. Use the "zoom" feature, Shift+F2, also available from the context menu, to edit such expressions. Note that Ctrl+Enter is still required to enter a newline in the zoom box.
When building text with multiple lines, it might be important to control where the line breaks can occur. For example, in "37 °C", there is a required space between the number and the unit, so the line cannot break between them. It is obtained by holding down the [Alt] key while composing "255" or "0160" on the number pad. It looks just like a space...
Concatenating Combo Boxes
When a combo box is used for concatenation, you often need to indicate which column to use. The bound
column is identical with the field stored in the table, usually a foreign key
of another table, while the value you want is probably the visible
column of the combo box.
On reports, combo boxes are used only for the translation they perform; the drop down arrow isn't printed, naturally. For example, a report of Products might contain a combo box displaying the name of the category. Let's say we want to use it to create the text "Beverages, ID 34" (the "category name" and later the "product ID"), in one line as a new text box:
= cboCategory.Column(1) & ', ID ' & [ProductID]
Note: You might see vanishing square brackets around "cboCategory" and even "Column", never mind them.
This syntax assumes that there is a combo box called "cboCategory" on the same report, with two columns: column zero
being the category ID, and column one
the name. It doesn't make much sense to have the category name appear twice on the report, so the combo box will be made invisible (property "visible: no").
A combo box is thus a simple way to translate a foreign key
(the category number) into something readable (the category name), even if the combo box itself is not visible, but used instead in another expression.
A more professional way to handle a foreign key would be to change the report's source to a query, thus eliminating the need for the combo box. If the "record source" was previously just the table name, "Products", it can be changed to a query like this:
SELECT Products.*, Categories.CategoryName
FROM Products LEFT JOIN Categories ON Products.CategoryID = Categories.CategoryID
The expression then becomes:
= [CategoryName] & ', ID ' & [ProductID]
... and the combo box can be eliminated. If you want to experiment in a scratch database, you can use one of the label reports from the article mentioned below; some of them contain the combo box for the Category field described here.
Concatenation is especially important for label reports, when there is little room and no place to grow; the sections in this article where originally part of the article Printing labels with Access
. The demo database attached to that article contains a few examples of concatenation, in particular the complex multi-line concatenation
building an address block.
However, concatenation isn't specific to label reports, and merited its own article.
I hope you have found something useful, a trick or a mystery explained, and that you will master concatenation in your own applications.
Markus G Fischer
about unrestricted Access