Help in parsing a field based on 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.

 

[james_lankford]

"Trying to NOT use VB to accomplish this (for a variety of reasons)."

no idea what the reasons are, but this is made for VB

create a module
put this code in it

'*****************************************************
Function get_field(this_index As Variant, f As Variant) As Variant
    
    If IsNull(f) Then
        get_field = Null
    Else
        Dim arr As Variant
        arr = Split(f, "/")
        If this_index > UBound(arr) Then
            get_field = Null
        Else
            get_field = arr(this_index)
        End If
    End If
    
End Function
'*****************************************************

then write your query

ARL: get_field( 0, [pipeline]![Short Description] )

BRANCHMGR: get_field( 1, [pipeline]![Short Description] )

DATE: get_field( 2, [pipeline]![Short Description] )

field4: get_field( 3, [pipeline]![Short Description] )

field5: get_field( 4, [pipeline]![Short Description] )