Crystal Concatenate Formula

I want a formula that only prints if the address line if it exists. If I have 6 lines and 3 are blank I would like it to look like this: -
(Without leaving blank lines between 3 and 6)

I have this formula but it misses Addr6: -
If(IsNull({Addr1})) then "" else
{Addr1} & Chr(13) & Chr(10) &
If(IsNull({Addr2})) then "" else
{Addr2} & Chr(13) & Chr(10) &
If(IsNull({Addr3})) then "" else
{Addr3} & Chr(13) & Chr(10) &
If(IsNull({Addr4})) then "" else
{Addr4} & Chr(13) & Chr(10) &
If(IsNull({Addr5})) then "" else
{Addr5} & Chr(13) & Chr(10) &
If(IsNull({Addr6})) then "" else

Can anyone help?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
A common way of dealing with this is to create multiple detail sections  (or whichever section you are using to display the address) and put each address line in a different section.  You can then set each section to Suppress when blank.
Author Commented:
Thanks for the suggestion but this address is on a customer statement and the format will not allow this ; (
Commented:
ANother way is to have a single text box
You can drag each field in in turn separated by CR

You can format the text box to suppress the blank lines

mlmcc
Commented:
The problem with your formula is that as soon as it finds a null field it ends

You are saying

if addr1 is null then do nothing
else  Concatenate the rest to Addr1

You need ( ) around the ifs but I am not sure how to use them

The way I generally do this is

Local StringVar strOut;

If Not IsNull(Addr2) then
If strOut = "" then
Else
strOut := strOut & chr(13) & chr(10) & Addr2;

If Not IsNull(Addr3) then
If strOut = "" then
Else
strOut := strOut & chr(13) & chr(10) & Addr3;

If Not IsNull(Addr4) then
If strOut = "" then
Else
strOut := strOut & chr(13) & chr(10) & Addr4;

If Not IsNull(Addr5) then
If strOut = "" then
Else
strOut := strOut & chr(13) & chr(10) & Addr5;

If Not IsNull(Addr6) then
If strOut = "" then
Else
strOut := strOut & chr(13) & chr(10) & Addr6;

strOut

mlmcc
Commented:
I think mlmcc is right.  You don't have the if-else statements separated, so the logic reads

""
else
<everything else>

Try this:

(
If(IsNull({Addr1})) then "" else
{Addr1} & Chr(13) & Chr(10)
)
&
(
If(IsNull({Addr2})) then "" else
{Addr2} & Chr(13) & Chr(10)
)
&
(
If(IsNull({Addr3})) then "" else
{Addr3} & Chr(13) & Chr(10)
)
&
(
If(IsNull({Addr4})) then "" else
{Addr4} & Chr(13) & Chr(10)
)
&
(
If(IsNull({Addr5})) then "" else
{Addr5} & Chr(13) & Chr(10)
)
&
(
If(IsNull({Addr6})) then "" else
)

James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
The brackets did it!! Thanks very much!
Commented:
You're welcome.  Glad I could help.

James
Commented:
Thanks James.  I didn't have a dataase setup to validate the syntax.  I thought it was something like that but wanted to test it first.

mlmcc
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.