[AccessD] Simplify String Concatenations with Nulls and Zero Length String

This tip just came through from FMS in their newsletter. Nothin gnew since
I've been using it for near a decade now but I just wanted to get thoughts
on it here. Anyone take issue with this method?

John B.

Tip #49: Simplify String Concatenations with Nulls and Zero Length Strings
Provided by: Luke Chung, FMS President

The concatenation of string values can be a nuisance when referencing fields
with Null values, because you want to avoid having extra spaces where the
missing string value should have appeared. 

A classic example of this is a person's name and their spouse. You want to
add the " and Spouse" to the name if the Spouse field is filled.

Here's the data:

PersonID	 FirstName	 LastName	 Spouse	
1	 Robert	 Jones	  	
2	 Thomas	 Smith	 Jane	
3	 Jack	 Thompson	 Sally	
4	 William	 Johnson	  	

And the results we want:

PersonID	 Person	
1	 Robert Jones	
2	 Thomas and Jane Smith	
3	 Jack and Sally Thompson	
4	 William Johnson	

Zero Length Strings Allowed


If the Spouse field allows zero length string values ("") -- its
AllowZeroLength property is True, you need to consider both Nulls and "".
You should use the NullToZero function NZ, to handle either case:

SELECT PersonID, FirstName & IIF(Nz(Spouse)<>"", " & " & Spouse) & " " &
LastName AS Person
FROM tblPerson

The NZ function evaluates the value it is passed. For text fields, it
returns "" if it's null or "". For numeric and dates fields, it returns zero
if it's null. This makes it easy to handle nulls in either case.


Zero Length Strings Not Allowed


If the Spouse field does not allow zero length strings, blank values are
always null. In this situation, we can avoid using a user-defined/built-in
function to test for null. The concatenation of a string value and a null
value will return a null value. Thus, you eliminate the extra processing
needed:

SELECT PersonID, FirstName & (" and " + Spouse) & " " & LastName AS Person
FROM tblPerson

By using the "+" command to combine the " and " and [Spouse] fields, the
result is nothing if the Spouse field is null. This is a difference from
using the "&" command which concatenates fields and would leave the " and "
if Spouse is null.