Solved

Crystal Concatenate Formula

Posted on 2012-04-04
8
474 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 34

Accepted Solution

by:
James0628 earned 300 total points
Comment Utility
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
Comment Utility
The brackets did it!! Thanks very much!
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You're welcome.  Glad I could help.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

18 Experts available now in Live!

Get 1:1 Help Now