Need help in parsing strings with multiple delimiters

[brosenfelt]

Hi guys -- brand new to Access -- but making good progress. Trying to NOT use VB to accomplish this (for a variety of reasons).



I have a field in our database with five distinct strings, separated by a delimited (a slash or "/").

I've been able to successfully write the formula to parse the first field:

ARL: Left$([pipeline]![Short Description],InStr(1,[pipeline]![Short Description],"/")-1)

The second field:

BRANCHMGR: Mid(Left([pipeline]![Short Description],InStr(InStr(1,[pipeline]![Short Description],"/")+1,[pipeline]![Short Description],"/")-1),InStr(1,[pipeline]![Short Description],"/")+1)

and the last field:

DATE: Mid$([pipeline]![Short Description],InStrRev([pipeline]![Short Description],"/")+1)

Having trouble figuring out how to get the third and fourth strings parsed. I know its going to include some nested InStr or InStrRev commands -- just having trouble figuring out the structure. Could potential need a LEN command as well.

Thanks for your assistance.

Typical field might look like this:

"Name1/Name2/Description/Update/Date"

Regards.

[orange]

cross posted http://www.access-programmers.co.uk/...d.php?t=280063
Please advise readers when you post the same or similar posts in another forum.

Please see this for more info.