Merging text fields

[Guest]

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.

 

[Jamie Collins]

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

By 'blank result' do you mean NULL or zero-length string? This will
handle both:

SELECT IIF(Address1 = '' OR Address1 IS NULL, '', Address1 & CHR$(13))
& IIF(Address2 = '' OR Address2 IS NULL, '', Address2 & CHR$(13))
& IIF(Address3 = '' OR Address3 IS NULL, '', Address3 & CHR$(13))
AS address_concat
FROM Test

....but will return either a concatenated address with a trailing
carriage return or a zero-length string. To adjust:

SELECT IIF(LEN(address_concat) = 0, '{{NO_ADDRESS}}',
LEFT$(DT1.address_concat, LEN(DT1.address_concat) - LEN(CHR$(13)))
) AS full_address
FROM
(
SELECT IIF(Address1 = '' OR Address1 IS NULL, '', Address1 & CHR$(13))
& IIF(Address2 = '' OR Address2 IS NULL, '', Address2 & CHR$(13))
& IIF(Address3 = '' OR Address3 IS NULL, '', Address3 & CHR$(13))
AS address_concat
FROM Test
) AS DT1;

Jamie.

--