Solved

Crystal Concatenate Formula

Posted on 2012-04-04
8
480 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 101

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
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 
LVL 101

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
 
LVL 35

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 35

Expert Comment

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

 James
0
 
LVL 101

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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