Access Query: Concatenation with Nulls

I've been trying to figure this out for a while now.  A part of a query that I often need to run is to add up several different things that may happen to a patient.  And a patient may have one, or none, or a couple or (heaven forbid!) 40 things happen to them over the course of a stay.

The standard way to concatenate in Access is by using the "&".  A query that I use multiple times a day starts with "Name: [last_name] & ", " & [First_name]" which results in "Hartshorn, Earle".  This is OK if both first and last name contain something (almost always).  But if you want to include middle name (almost always empty for my purposes) you would use Name: [last_name] & "," & " " & [First_name] &" "&[Middle_name].  this would result in "Hartshorn, Earle   "; if there is no middle name then there would be an extra space.  Not a big deal in this case, but what if you needed to include many fields that are hopefully blank?  Lots of blank spaces are what you would get, and it looks ugly and is confusing, and when you add commas between terms, you get this:

term, , , term, , , , , , term, , , ,  Urghh.

I decided to research the problem this morning and found this post.  When concatenating 40 or more fields it takes some time to write the query string, but it works very nicely.

Cross posted from http://www.spearsofttech.com/articles/microsoft-access-query-tip-concatenation-with-nulls/

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:
Spearman,
, Brad
Johnson,
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:
Spearman
Brad
Johnson

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.

Seth Spearman

Original from http://www.spearsofttech.com/articles/microsoft-access-query-tip-concatenation-with-nulls/

The very first time that I used this method to concatenate fields it worked beautifully.  The second time I tried to use it I kept having an error:  The field aliases would be, for example, "Rem0" and "Rem1" and the Query String would be "All: ([Rem0]+", ") & ([Rem1]+", ")" but every time that I tried to run the query it would ask me to input the value of "Rem0" and "Rem1".  If I just hit "OK" on each of those the answers would come thru OK, but I couldn't figure out why it was doing that.  After staring at it and comparing the working query to the new query I suddenly realised that the "Total" row in the new query was set to "Where"!  I deleted "Where" and the query now works!

Until you add a numerical Value to the report, that is.  :)