Help with parsing string with multiple delimiters (1 Viewer)

[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.

 

[jdraw]

Research the Split() function

 

[brosenfelt]

I've tried to use the SPLIT function, but it seems to crash when there is inconsistent data in any of the fields. That's why I'm leaning towards using native functions. Is there a way to put some error correction into the code when using SPLIT?

 

[jdraw]

Sample

Sub brosenfelt()
    Dim inComing As String
    Dim i As Integer
    Dim vArr As Variant
    inComing = "Name1/Name2/Description/Update/Date"
    vArr = Split(inComing, "/")
    For i = LBound(vArr) To UBound(vArr)
        Debug.Print vArr(i)
    Next i
End Sub

Output:
Name1
Name2
Description
Update
Date

 

[arnelgp]

if your using it in query expression:

SELECT Left(([pipeline]![Short Description],InStr(([pipeline]![Short Description],"/")-1) AS ARL, 
Left([A1],InStr([A1],"/")-1) AS BRMGR, 
Left([A2],InStr([A2],"/")-1) AS DESCR, 
Left([A3],InStr([A3],"/")-1) AS [UPDATE], 
Replace(([pipeline]![Short Description],[ARL] & "/" & [BRMGR] & "/" & [DESCR] & "/" & [UPDATE] & "/","") AS [DATE], 
Replace(([pipeline]![Short Description],[ARL] & "/","") AS A1, Replace(([pipeline]![Short Description],[ARL] & "/" & [BRMGR] & "/","") AS A2, 
Replace(([pipeline]![Short Description],[ARL] & "/" & [BRMGR] & "/" & [DESCR] & "/","") AS A3
FROM pipeline;

that is if you do not want to use VBA.

 

[jdraw]

cross posted
http://www.accessforums.net/access/need-help-parsing-strings-multiple-delimiters-54382.html

 

[brosenfelt]

Thank you all for the help! I was able to create a custom function using the SPLIT vb command that is working great!

 

[jdraw]

Glad you have it working.
You could post your function -- it may help someone else.

 

[NauticalGent]

Quick question along this thread; if the delimiter has the possibility of being two different things (i.e. a "/" or a " "), is there a way to allow for that?

 

[plog]

Quick question along this thread; if the delimiter has the possibility of being two different things (i.e. a "/" or a " "), is there a way to allow for that?

In the immortal words of every grade school math teacher ever: Reduce the problem and then solve using a known method.

So, use the Replace function (https://www.techonthenet.com/oracle/functions/replace.php) to change one of the delmiters to the other. Then implement the solution listed above.

 

[NauticalGent]

In the immortal words of every grade school math teacher ever: Reduce the problem and then solve using a known method.

Brillant!

So, use the Replace function (https://www.techonthenet.com/oracle/functions/replace.php) to change one of the delmiters to the other. Then implement the solution listed above.

Ridiculously easy... I am embarrassed to no end because I use the Replace() throughout this procedure.

Thanks for helping me get out of my own way!

Now pardon me while I go flog myself ruthlessly...