Solved

How to escape : in formula

Posted on 2006-11-16
9
333 Views
Last Modified: 2006-11-18
Hi,

I have the following formula in Crystal syntax for my mail to address .

On certain data set, it did not return the formated address, while others work just fine.  I suspect it is because in my head_add1, the actual data has a : in it, such as ATTN: Joe Doe. How do I escape that?

stringVar out := '';
if not isnull({Invoice.head_add1}) then
   out := out + {Invoice.head_add1} + chr(10) + chr(13);
if not isnull({Invoice.head_add2}) and ({Invoice.head_add2})<>'' then
   out := out + {Invoice.head_add2} + chr(10) + chr(13);
if not isnull({Invoice.head_City}) then
   out := out + {Invoice.head_City} + ',' + {Invoice.head_state} + ' ' +{Invoice.head_postal_Code} + ' ' + {Invoice.head_country};
out;

Thanks!
0
Comment
Question by:ommer
[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
  • 4
  • 4
9 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 17961701
Please give me some sample rows of the field {Invoice.head_add1} from your data table..I need to see what it looks like...

thanks
MikeV
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 17962415
Try something like this

stringVar out := '';
numbervar ColonPlace;
if not isnull({Invoice.head_add1}) then
(
   ColonPlace := instr(":",{Invoice.head_add1});
   if ColonPlace > 0 then
       out := out + mid({Invoice.head_add1},ColonPlace+1)  + chr(10) + chr(13);
   else
       out := out + {Invoice.head_add1} + chr(10) + chr(13);
)
if not isnull({Invoice.head_add2}) and ({Invoice.head_add2})<>'' then
   out := out + {Invoice.head_add2} + chr(10) + chr(13);
if not isnull({Invoice.head_City}) then
   out := out + {Invoice.head_City} + ',' + {Invoice.head_state} + ' ' +{Invoice.head_postal_Code} + ' ' + {Invoice.head_country};
out;

mlmcc
0
 

Author Comment

by:ommer
ID: 17965595
Thanks! I understand your approach.
I got a syntax error "The ) is missing" with the ELSE highlighted. I did not find any missing ).

Sample data for head_add1:
Normally they look like these:
38 Plant Rd
4198 Town Center Ave
But could also like:
ATTN: Joe Doe
Or
Split Ship
If it is ATTN, just mix it with the rest of the address, and if it is Split Ship, don't show anything at all.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 101

Expert Comment

by:mlmcc
ID: 17965860
>>I got a syntax error "The ) is missing" with the ELSE highlighted. I did not find any missing ).
The problem isn't a missing ) but rather an extra ;.
Delete the ; before ELSE
   if ColonPlace > 0 then
       out := out + mid({Invoice.head_add1},ColonPlace+1)  + chr(10) + chr(13)
   else
       out := out + {Invoice.head_add1} + chr(10) + chr(13);

If there is a : will you always have ATTN:

Try this
if not isnull({Invoice.head_add1}) then
(
   if Left({Invoice.head_add1},10) = "ATN:"  then
       out := out + mid({Invoice.head_add1},6)  + chr(10) + chr(13);
   else if  Left({Invoice.head_add1},5) <> "Split Ship"  then
       out := out + {Invoice.head_add1} + chr(10) + chr(13);
)

mlmcc
0
 

Author Comment

by:ommer
ID: 17966343
Thanks! I made little adjustment as:
if not isnull({Invoice.head_add1}) then
(
   if Left({Invoice.head_add1},5) = "ATTN:"  then
       out := out + mid({Invoice.head_add1},6)  + chr(10) + chr(13)
   else if  Left({Invoice.head_add1},10) <> "Split Ship"  then
       out := out + {Invoice.head_add1} + chr(10) + chr(13);
)

It still wouldn't show data with :. The whole return is empty. Let me try to take it out in sql.

But it did pick up "Split Ship" nicely. By the way, how do I exit out in the middle? I want to return empty when it has Split Ship, i.e. no need to concat city, state etc.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 100 total points
ID: 17966549
Probably the easiest is

WhilePrintingRecords;
stringVar out;
out := '';
if not isnull({Invoice.head_add1}) then
(
   if  Left({Invoice.head_add1},10) <> "Split Ship"  then
   (    
       if Left({Invoice.head_add1},5) = "ATTN:"  then
           out := out + mid({Invoice.head_add1},6)  + chr(10) + chr(13)
       else
           out := out + {Invoice.head_add1} + chr(10) + chr(13);
       if not isnull({Invoice.head_add2}) and ({Invoice.head_add2})<>'' then
          out := out + {Invoice.head_add2} + chr(10) + chr(13);
       if not isnull({Invoice.head_City}) then
          out := out + {Invoice.head_City} + ',' + {Invoice.head_state} + ' ' +{Invoice.head_postal_Code} + ' ' + {Invoice.head_country};
    )
)
out

mlmcc
0
 

Author Comment

by:ommer
ID: 17966604

Fantastic!

Thanks a lot!
0
 

Author Comment

by:ommer
ID: 17966644
Oh, one more thing, what is this "WhilePrintingRecords;"? Can I leave it out? Will that make it only work on print but not preview?


0
 
LVL 101

Expert Comment

by:mlmcc
ID: 17968992
It will work when running the report.  It means the formula will run when the records are being displayed or previewed.

mlmcc
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 …
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

688 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