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.

Jobs

Split dates from string with } delimited into individual fields

Split dates from string with } delimited into individual fields

(OP)
Ok I'm stuck on this one and need some help. I've got a WOSummary.DepositPostedDate string field. This field shows dates deposits were taken on an order separated by a }. The field looks like this...

05/08/2017 or 05/08/2017}05/09/2017 or 05/08/2017}05/09/2017}05/10/2017 and so on depending on how many deposits were taken on the order.

I need a formula that will split these into separate fields. So if there's only a single date, return only that date. If there's two dates separated by a }, I need one field to show the first date and one field to show the second date. If there's three dates, I need one field to show the first date, one to show the second, and one to show the third, etc.

There can be any number of dates in this field but I don't think anyone would take more than 5 deposits on a single order.

RE: Split dates from string with } delimited into individual fields

I would think you would need to use the split function and store it in a array. Below is an one example on how that might be done.

stringvar array result := Split ('05/08/2017}05/09/2017}05/10/2017','}' );
numbervar i;
stringvar out;
for i := 1 to count(result) do
(
out := out+result[i] + chr(10)
);
out

RE: Split dates from string with } delimited into individual fields

So the field only has more than 1 date if the length is > 10. If the dates are always entered in that format (2 digit month, 2 digit day, 4 digit year), you can just use Left({DepositPostedDate}, 10) and Mid({DepositPostedDate}, 12, 10) and Right({DepositPostedDate}, 10). If not you'd have to use InStr to find the }, then take the left, mid and right.
I hope that helps.

RE: Split dates from string with } delimited into individual fields

If you want every date returned in a separate formula, you will need to do it this way:

1st Date:
If UBound(Split({Table.Field}, '}')) >= 1
Then Split({Table.Field}, '}')[1]

2nd Date
If UBound(Split({Table.Field}, '}')) >= 2
Then Split({Table.Field}, '}')[2]

3rd Date
If UBound(Split({Table.Field}, '}')) >= 3
Then Split({Table.Field}, '}')[3]

... and so on.


If you want them all in a single formula, one under the other do it this way:

CODE

WhilePrintingRecords;
Local StringVar RESULT;
Local Numbervar i;


For i := 1 to UBound(Split({Table.Field}, '}'))
Do  RESULT := RESULT + Split({Table.Field}, '}')[i] + CHR(10);

RESULT 

Hope this helps.

Cheers
Pete

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!

Resources

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