Combining Fields - null values

[kristinac]

I am running a query that takes Streets.Name and Streets.Type and combines them into a field called Street. Sometimes Streets.Type is NULL. When it is NULL, I get nothing in Street - empty string. Is there a way to say in the select statement, to use Streets.Name for Street if Streets.Type is NULL?

I tried some things I found in other posts, but I haven't found anything that will work yet.

SELECT Streets.City, Streets.Name + ' '+ Streets.Type AS Street...

Please help. I would appreciate any advice...

 

[TonyJollans]

Hi kristinac,

Using the "+" concatenation operator ..

Streets.Name + ' '+ Streets.Type

.. is treated as a single expression which, because it involves a null is itself null.

If you use the "&" concatenation operator ..

Streets.Name & ' ' & Streets.Type

.. each element of the expression is treated individually and the non-null elements will be included in the result.

A better alternative would be to use the NZ function ..

NZ(Streets.Name) & ' ' & Nz(Streets.Type)

.. which (by default) returns an empty string for your null values.

Enjoy,
Tony

 

[kristinac]

Thanks Tony! I couldn't use the NZ - my pgm didn't like it. But the & worked great. It's doing exactly what I needed.
I appreciate it!