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
Solved

How to escape : in formula

Posted on 2006-11-16
9
331 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
  • 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 100

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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
LVL 100

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 100

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 100

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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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. …
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

838 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