how do you disregard a NULL when concatenating

[plshelpme]

I am trying to merge several columns into one in a view.
The problem is sometimes some of these columns contain a NULL value. So when I do; Col1 + ' ' + Col2 + ' ' + Col3
If any of these columns has a NULL value in it, the result is NULL, and not just the other two values concatenated toghether.

Any one see a way around this?

Thanks

 

[mikey69]

I would do this by using Iif and Isnull functions in the concatenation and returning a zero length string if a null is detected. If your field is field1 do this:

Iif(Isnull(field1),"",field1)

 

[plshelpme]

i am getting the error message that ISNULL requires two arguments??

 

[plshelpme]

its just ISNULL(field1,'') ...thanks though ;-)