Solved

How to escape : in formula

Posted on 2006-11-16
9
329 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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 video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

919 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

20 Experts available now in Live!

Get 1:1 Help Now