May 03 2010

Microsoft Access Query Tip – Concatenation With Nulls

I wonder, should I even assume that you know what that “concatsomething” word means. Since this is the “beginning” tip I’ll assume you don’t. But if you already know what concatenation is, then skip the next two paragraph of this section.

Concatenation, to get straight to the point, is joining two values in a query (or in programming code) to produce one value. Let’s use two fields, FirstName and LastName, as an example. Many beginners would, in fact, be tempted to create only one field (FullName) in a database because they don’t know how to concatenate fields in queries. Concatenation in queries allows you to “create” the FullName field in the query at the moment that the query is run.

The way this is done is by using the concatenation operator, which is the ampersand (&), in the query. After dropping in a table, in the QBE grid (that is the grid in the lower half of the query design window) click in the next blank column of the grid (into the Field cell) and type the following:
FullName: [FirstName] & " " & [LastName].
When you run the query you would see a FullName field with FirstName and LastName concatenated together.

Now for our trick. Many beginners are not aware that the plus(+) operator can also be used for string (text) concatenation. Concatenating with a plus(+) works slightly differently than concatenating with the ampersand when concatenating with Null values. (Note: A Null is an empty or blank value which is not the same as a “zero-length string”). Let me just state the difference and then I’ll explain. KEY CONCEPT: Concatenating a string with a Null using an ampersand(&) returns a string. (Null & String = String) Concatenating a string with a Null using a plus (+) returns a Null. (Null + String = Null) This is an important distinction that you can use to your advantage when creating queries. Let me explain. Let’s use the name example as above except let’s put the LastName in first and use a comma. Using the concatenation operator we would create a field like this:
FullName: [LastName] & ", " & [FirstName]
The problem with this method is that if the LastName or FirstName field is Null in some of the rows then it will return ugly results like the following:
, Brad

This problem can be handled by using the plus(+) operator in a special way. Using the plu(+) operator we would create a field like this:
FullName: ([LastName]+", ") & [FirstName]
This method fixes our problem and would return the following nice, neat rows:

Now for a final explanation of why the second method returned better results as compared to the first. In the Spearman example (where there is a Null FirstName field), using the ampersand, the value is created as Null & “, ” & Spearman and returns “,Spearman”. But in the second example, using the plus, the value is created as Null + “, ” which returns a Null which is then concatenated to Spearman thereby returning “Spearman” as the value. In other words, a Null is concatenated to Spearman.

Give it a try and see if that works for you.