Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Crystal Concatenate Formula

Posted on 2012-04-04
8
Medium Priority
?
482 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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 800 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 1200 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 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