Concatenating null to string using + results in null

Question

I have the following SQL Query in Access:

SELECT ID, CurrencyName + ' (' + CurrencySymbol + ')' AS [Currency], 
CurrencyLocation, CurrencySymbol FROM Currencies ORDER BY SortOrder

What I noticed is that I get a full table of results except if if the field CurrencySymbol is left NULL or empty. If the CurrencySymbol field is null, rather than Concatenate nothing, Access skips over the record and continues as shown below

.

Did I get something wrong or is there a better way to write this Query?

Answer 1

If you concatenate strings with +, string + NULL yields NULL.

If you concatenate strings with &, string & NULL yields string.

Thus, you have two options to fix this:

Option 1: CurrencyName + ' (' + Nz(CurrencySymbol, '') + ')'. Nz (NullToZero) converts Null values to its second argument.

Option 2: CurrencyName & ' (' & CurrencySymbol & ')'

You can use this fact to create an expression that only shows the parenthesis when a currency symbol is present (Credit for this idea goes to this blog post):

CurrencyName & (' (' + CurrencySymbol + ')') will yield Points and Euro (€).

Answer 2

That's because concatenating a string and NULL results in NULL.

SELECT ID, CurrencyName + ' (' + Iif(IsNull(CurrencySymbol), '', CurrencySymbol) + ')'
  AS [Currency], CurrencyLocation, CurrencySymbol
FROM Currencies
ORDER BY SortOrder