Merging text fields

[dtfrancis15uk]

In trying to import an excel spreadsheet to Outlook for contacts, I need to
merge three address fields into one (They are called Address1, Address2 and
Address3).

When doing this in Access by saying (for example) [Address1] + ',' +
[Address2] I get a blank result field when either of Address1 or Address2 is
blank. How can I get over this?

Also, instead of a , between the field values, I would like a carriage
return - is this possible and how?

Many thanks

 

[Brendan Reynolds]

You can take advantage of the different behaviour of the '+' and '&'
operators when concatenating strings. When you use the '+' operator, if any
part of the expression is Null, the result of the expression is Null. For
example, in the Immediate Window ...

? "one" + null
Null

The '&' operator gives a different result. For example, in the Immediate
Window ...

? "one" & null
one

You can use this difference both to avoid having the entire expression
evaluate as Null, and also to avoid a blank line in the middle of your
address. For example, in the Immediate Window again ...

? ("Address1" + Chr$(13) + Chr$(10)) & (Null + Chr$(13) + Chr$(10)) &
("Address3" + Chr$(13) + Chr$(10))
Address1
Address3

Note the use of parentheses and '+' and '&' operators, to avoid having the
whole expression evaluate as Null, and also to avoid a blank line between
"Address1" and "Address3", despite the presence of the Null value between
them.

Chr$(13) and Chr$(10) (in that order) will insert a carriage return and line
feed to start a new line. In VBA code you can use the intrinsic constant
vbCrLf instead, but in expressions and queries you need to use the Chr$()
function.