×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to extract data from a string

How to extract data from a string

How to extract data from a string

(OP)
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
 

RE: How to extract data from a string

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?
 

RE: How to extract data from a string

(OP)

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

RE: How to extract data from a string

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close