Null values in merged query fields

[Joe Ventre]

I am doing a query that merges several fields into one in the result. The
problem is that if any of the fields being merged together contains a null
value then the resulting field is null. For example: the query creates a
field by mergin A, B, C and D together by using the experssion A+B+C+D. If
each of the fields contains a non-null value the result is correct but if
any of them contains a null value the resulting field is null rather than
just skipping the one null field. If I replace the null value with "" then
query works. How can I get around this with having to replace every null
with "" before doing the query. I tried using iif to test the value but
that didn't work either (iif [A] null,"",[A]) + iif[B} null.....).

Thanks

 

[Guest]

Use the follwing --
(iif [A] Is Null,"",[A]) & iif Is Null.....).

Or you may want spacing --
(iif [A] Is Null,"",[A]&" ") & iif Is Null.....).

 

[Duane Hookom]

Use the ampersand "&" rather than "+". [A] & & [C].