removing blank lines from addresses in a report

[Debbie S.]

How do you force a report to remove blank lines from address fields (like
billing and shipping addresses on an invoice)? I tried using the can grow and
can shrink properties, but because I have put the billing and shipping
addresses horizontally across from each other on the page, rather than one on
top of the other, the can shrink property is not working. I know there is a
way to do it using "trim" but I'm not sure what that is or the appropriate
way to code it. If someone could please help me figure this out I'd
appreciate it. My addresses appear on the invoice as follows:

Billing Address Shipping Address
First Name Last Name ShipName
Organization Name ShipAddress
123 Street Ship City, State Zip
Main City, State Zip ShipCountry
Country

Thanks. -DS

 

[Allen Browne]

You may be able to do this by combining the fields into one text box, like
this:

=([First Name] + " " & [LastName]) + Chr(13) + Chr(10) &
([Organization name] + Chr(13) + Chr(10)) &
([Address] + Chr(13) + Chr(10)) & ...

 

[Debbie S.]

Thank you, this was a big help, it worked like a charm. What is "Chr" and
what are the numbers in parentheses? -ds

You may be able to do this by combining the fields into one text box, like
this:

=([First Name] + " " & [LastName]) + Chr(13) + Chr(10) &
([Organization name] + Chr(13) + Chr(10)) &
([Address] + Chr(13) + Chr(10)) & ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How do you force a report to remove blank lines from address fields (like
billing and shipping addresses on an invoice)? I tried using the can grow
and
can shrink properties, but because I have put the billing and shipping
addresses horizontally across from each other on the page, rather than one
on
top of the other, the can shrink property is not working. I know there is
a
way to do it using "trim" but I'm not sure what that is or the appropriate
way to code it. If someone could please help me figure this out I'd
appreciate it. My addresses appear on the invoice as follows:

Billing Address Shipping Address
First Name Last Name ShipName
Organization Name ShipAddress
123 Street Ship City, State Zip
Main City, State Zip ShipCountry
Country

Thanks. -DS

 

[Allen Browne]

Chr() is a function that generates a character.
Chr(65) is "A"; Chr(66) is "B", and so on.

Characters below 32 are non-printable control characters.
Chr(13) is a carriage return. Chr(10) is a line feed.
Therefore after a Chr(13) and a Chr(10), the cursor is at the beginning of
the next line.

There is also a subtle difference between the 2 concatenation operators, &
and +, when it comes to handling nulls:
"A" & Null => "A"
"A" + Null => Null
We used + within the line to all shrink to Null, but & between the lines so
a null line does not eliminate the other lines.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you, this was a big help, it worked like a charm. What is "Chr" and
what are the numbers in parentheses? -ds

You may be able to do this by combining the fields into one text box,
like
this:

=([First Name] + " " & [LastName]) + Chr(13) + Chr(10) &
([Organization name] + Chr(13) + Chr(10)) &
([Address] + Chr(13) + Chr(10)) & ...

How do you force a report to remove blank lines from address fields
(like
billing and shipping addresses on an invoice)? I tried using the can
grow
and
can shrink properties, but because I have put the billing and shipping
addresses horizontally across from each other on the page, rather than
one
on
top of the other, the can shrink property is not working. I know there
is
a
way to do it using "trim" but I'm not sure what that is or the
appropriate
way to code it. If someone could please help me figure this out I'd
appreciate it. My addresses appear on the invoice as follows:

Billing Address Shipping Address
First Name Last Name ShipName
Organization Name ShipAddress
123 Street Ship City, State Zip
Main City, State Zip ShipCountry
Country

Thanks. -DS