Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

How to escape : in formula

Posted on 2006-11-16
9
Medium Priority
?
336 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 400 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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. …
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

647 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