Crystal Reports Suppress part of Address Field if the value is null

Hello,

I am working with Crystal Reports 11.  I am writing a formula to combine 3 address fields together.  However, one of the fields contains null values.  When it does contain a null value I want the extra space to go away.  

This is how the database looks:

Address1                     Address2               Address3
10 Main Street            Suite 5                    Boston, MA, 02118
20 Pleasant Street                                      Medford, MA, 02125

There is no Address 2 for the second line.

So my formula is this:

{Test.Address_1}+
ChrW(13)+
(If IsNull({Test.Address_2}) then
"" else {Test.Address_2})
+ChrW(13)
+ {Test.Address_3}

Result for Line 1:

10 Main Street
Suite 5
Boston, MA, 02118

Result for Line 2
20 Pleasant Street

Medford, MA, 02125

I want to remove the blank space between "20 Pleasant Street" and "Medford, MA, 02125" for the second result.

Thanks for any help,

Mike
Michael GrahamData AnalystAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
Try it this way

  {Test.Address_1} +  ChrW(13) +
 (
    If IsNull({Test.Address_2})  then
          "" 
    else 
         {Test.Address_2}  + ChrW(13)
)
 + {Test.Address_3}

Open in new window


Another way would be using what you have

Replace( {Test.Address_1}+
 ChrW(13)+
 (If IsNull({Test.Address_2}) then
 "" else {Test.Address_2})
 +ChrW(13)
 + {Test.Address_3}, ChrW(13) + ChrW(13), ChrW(13))

Open in new window


Another method

  {Test.Address_1} +  ChrW(13) +
 (
    If NOT  IsNull({Test.Address_2})  then
         {Test.Address_2}  + ChrW(13)
)
 + {Test.Address_3}

Open in new window


mlmcc
0
 
Michael GrahamData AnalystAuthor Commented:
Tried the first formula and it was perfect.  Awesome.  Thank you so much.
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.