expression to concatenate text strings omitting empty records?

[dbnDavid]

I want to stack four mailing address lines into a single text box for a report.
I've managed to build an expression to concatenate the four text strings
with line breaks, like this:
[Subclients]![Address1] & Chr(13) & Chr(10) & [Subclients]![Address2] &
Chr(13) & Chr(10) & [Subclients]![Address3] & Chr(13) & Chr(10) &
[Subclients]![Country] ... etc.
but I don't know how to force the stack to close up when one of the strings
is empty - e.g some addresses don't require all three lines before "Country"
but I don't want the address to display with a gap.

 

[Douglas J Steele]

Take advantage of the fact that using + as the concatenation symbol
propagates Nulls:

[Subclients]![Address1] & (Chr(13) & Chr(10) + [Subclients]![Address2]) &
(Chr(13) & Chr(10) + [Subclients]![Address3]) & (Chr(13) & Chr(10) +
[Subclients]![Country])

 

[Klatuu]

[Subclients]![Address1] & Iif(IsNull([Subclients]![Address1]), Null, Chr(13)
& Chr(10)) & [Subclients]![Address2] & Iif(IsNull([Subclients]![Address2]),
Null,
Chr(13) & Chr(10)) & [Subclients]![Address3] &
Iif(IsNull([Subclients]![Address3]), Null, Chr(13) & Chr(10)) &
[Subclients]![Country]

 

[dbnDavid]

Thanks a lot. This is what I needed.

[Subclients]![Address1] & Iif(IsNull([Subclients]![Address1]), Null, Chr(13)
& Chr(10)) & [Subclients]![Address2] & Iif(IsNull([Subclients]![Address2]),
Null,
Chr(13) & Chr(10)) & [Subclients]![Address3] &
Iif(IsNull([Subclients]![Address3]), Null, Chr(13) & Chr(10)) &
[Subclients]![Country]

I want to stack four mailing address lines into a single text box for a report.
I've managed to build an expression to concatenate the four text strings
with line breaks, like this:
[Subclients]![Address1] & Chr(13) & Chr(10) & [Subclients]![Address2] &
Chr(13) & Chr(10) & [Subclients]![Address3] & Chr(13) & Chr(10) &
[Subclients]![Country] ... etc.
but I don't know how to force the stack to close up when one of the strings
is empty - e.g some addresses don't require all three lines before "Country"
but I don't want the address to display with a gap.

 

[dbnDavid]

Thanks Doug, but this solution isn't perfect. It eliminates the blank lines
but it inserts one of those little empty box characters at the end of the
preceding line for each blank line that has been closed up. I've now tried
adding

& Iif(IsNull([Subclients]![Address1]), Null,

as suggested by the post here from Klatuu, and that solves the problem.

Take advantage of the fact that using + as the concatenation symbol
propagates Nulls:

[Subclients]![Address1] & (Chr(13) & Chr(10) + [Subclients]![Address2]) &
(Chr(13) & Chr(10) + [Subclients]![Address3]) & (Chr(13) & Chr(10) +
[Subclients]![Country])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I want to stack four mailing address lines into a single text box for a report.
I've managed to build an expression to concatenate the four text strings
with line breaks, like this:
[Subclients]![Address1] & Chr(13) & Chr(10) & [Subclients]![Address2] &
Chr(13) & Chr(10) & [Subclients]![Address3] & Chr(13) & Chr(10) &
[Subclients]![Country] ... etc.
but I don't know how to force the stack to close up when one of the strings
is empty - e.g some addresses don't require all three lines before "Country"
but I don't want the address to display with a gap.

 

[Douglas J Steele]

Are you sure you typed it just as I said to? You didn't possibly reverse the
order of Chr(13) & Chr(10), or leave one of the two expressions out, did
you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug, but this solution isn't perfect. It eliminates the blank lines
but it inserts one of those little empty box characters at the end of the
preceding line for each blank line that has been closed up. I've now tried
adding

& Iif(IsNull([Subclients]![Address1]), Null,

as suggested by the post here from Klatuu, and that solves the problem.

Take advantage of the fact that using + as the concatenation symbol
propagates Nulls:

[Subclients]![Address1] & (Chr(13) & Chr(10) + [Subclients]![Address2]) &
(Chr(13) & Chr(10) + [Subclients]![Address3]) & (Chr(13) & Chr(10) +
[Subclients]![Country])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I want to stack four mailing address lines into a single text box for

a
report.

I've managed to build an expression to concatenate the four text strings
with line breaks, like this:
[Subclients]![Address1] & Chr(13) & Chr(10) & [Subclients]![Address2] &
Chr(13) & Chr(10) & [Subclients]![Address3] & Chr(13) & Chr(10) &
[Subclients]![Country] ... etc.
but I don't know how to force the stack to close up when one of the strings
is empty - e.g some addresses don't require all three lines before "Country"
but I don't want the address to display with a gap.

 

[dbnDavid]

Hi Doug. I've been trying this again on a fresh query, and I didn't type the
expression. I copied and pasted it from your posting, just like I did the
first time, and the error character is still there at the end of every line
that is followed by a closed up blank line. When it's two or three blank
lines that have been closed the equivalent number of error characters display.

Are you sure you typed it just as I said to? You didn't possibly reverse the
order of Chr(13) & Chr(10), or leave one of the two expressions out, did
you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug, but this solution isn't perfect. It eliminates the blank lines
but it inserts one of those little empty box characters at the end of the
preceding line for each blank line that has been closed up. I've now tried
adding

& Iif(IsNull([Subclients]![Address1]), Null,

as suggested by the post here from Klatuu, and that solves the problem.

Take advantage of the fact that using + as the concatenation symbol
propagates Nulls:

[Subclients]![Address1] & (Chr(13) & Chr(10) + [Subclients]![Address2]) &
(Chr(13) & Chr(10) + [Subclients]![Address3]) & (Chr(13) & Chr(10) +
[Subclients]![Country])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I want to stack four mailing address lines into a single text box for a
report.
I've managed to build an expression to concatenate the four text strings
with line breaks, like this:
[Subclients]![Address1] & Chr(13) & Chr(10) & [Subclients]![Address2] &
Chr(13) & Chr(10) & [Subclients]![Address3] & Chr(13) & Chr(10) &
[Subclients]![Country] ... etc.
but I don't know how to force the stack to close up when one of the
strings
is empty - e.g some addresses don't require all three lines before
"Country"
but I don't want the address to display with a gap.