Combining a null value into a string using the 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 

I noticed that I get a full table of results, unless the CurrencySymbol field is NULL or empty. If the CurrencySymbol field is NULL and not "Concatenate anything", Access skips the entry and continues, as shown below.

.

I got something wrong or is there a better way to write this request?

Answer 1

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

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

Thus, you have two options to fix this:

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

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

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

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

Answer 2

This is because string and NULL concatenation results in NULL.

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