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: -
Addr1
Addr2
Addr3
Addr6
(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
{Addr6}

Can anyone help?
HKFueyAsked:
Who is Participating?
 
James0628Connect With a Mentor Commented:
I think mlmcc is right.  You don't have the if-else statements separated, so the logic reads

If(IsNull({Addr1})) then
  ""
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
{Addr6}
)


 James
0
 
peter57rCommented:
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.
0
 
HKFueyAuthor Commented:
Thanks for the suggestion but this address is on a customer statement and the format will not allow this ; (
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mlmccCommented:
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
0
 
mlmccConnect With a Mentor 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;

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

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

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

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

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

strOut

mlmcc
0
 
HKFueyAuthor Commented:
The brackets did it!! Thanks very much!
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
 
mlmccCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.