How to extract data from a string

[iwm]

I created this report using Crystal Reports 9 several years ago. The description field is a string containing the invoice date and the item description.

The client instructions for formatting from the description field were as follows:
1. (First character) Open parenthesis
2. Followed by mm/dd/yyyy date format (slash will be used to separate the date segments).
3. (Eleventh character) Closed parenthesis
4. Followed by item description

Example: (12/15/2010)office supplies

I used the following formula to create the invoice date field:

If left({ProcItem.description}, 1) = chr(40) then
datevalue(Mid ({ProcItem.description}, 2,10))

And I used the following formula to create the item description field:

If left({ProcItem.description}, 1) = chr(40)
then Mid ({ProcItem.description}, 13)
else {ProcItem.description}

They have recently decided they would like to be able to enter the date using any recognized date format. The updated instructions are as follows:

1. (First character) open parenthesis
2. Followed by any recognized date format (slash will be used to separate the date segments)
3. Closed parenthesis
4. Followed by item description

I need help creating two new formulas, the invoice date formula and the description formula.

Your assistance is greatly appreciated.

Ingrid

 

[fisheromacse]

so...if they enter 01/04/2011 and 04/01/2011 and 11/01/04 and 01/04/11 and 04/01/11. supposedly all for January 4, 2001, how would you be able to know that?

 

[iwm]

They want the characters that are in the parenthesis to become the the invoice date field, and remaining characters will
become the description field. If there are no parenthesis then the entire string should be used as the description field.

Your assistance is greatly appreciated.

Thanks,

Ingrid

 

[fisheromacse]

i don't have crystal in front of me, so just off the top of my head, and i apologize for any errors, but something like this:

//{@Findinthemiddle}
numbervar fst := instr({yourtable.yourfield},'(');
numbervar lst := instr({yourtable.yourfield},')');
MID({yourtable.yourfield},fst,lst)


If you are getting close but off by 1 try adding or subtracting fromt the variables....change your MID line to a variation like this: MID({yourtable.yourfield},fst+1,lst-1)