Solved

Crystal Concatenate Formula

Posted on 2012-04-04
8
475 Views
Last Modified: 2012-04-05
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?
0
Comment
Question by:HKFuey
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37806187
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
 

Author Comment

by:HKFuey
ID: 37806243
Thanks for the suggestion but this address is on a customer statement and the format will not allow this ; (
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37808934
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 200 total points
ID: 37808954
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 34

Accepted Solution

by:
James0628 earned 300 total points
ID: 37809560
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
 

Author Closing Comment

by:HKFuey
ID: 37809824
The brackets did it!! Thanks very much!
0
 
LVL 34

Expert Comment

by:James0628
ID: 37809880
You're welcome.  Glad I could help.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37811616
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now