Solved Split String in Query based on last delimiter

[Paul-Learning]

Hi,
I'm new to Access (using office 365) and so far it's all going well but I'm a bit stuck. I'm trying to split (in a select qry) part of a text string (which is a file path) eg:

Field "Path" eg D:\Music\CD Collection\2000\Rock\

what I'm trying to achieve is, irrespective of the number of "Parent" folders in the string just want to split out the final folder, in this case "Rock" and show this as a separate "field" on the datasheet view.

All the examples I could find were applicable to Excel. I have got the hang of "Right" function in Access but not sure how to use it with a variable number of characters.

Thanks if you can help

Paul

 

[MajP]

There are lots of ways to do it. I like to make a function so I can do a lot of error checking. Try this

Public Function SplitFolder(varFolder As Variant) As String
  Dim arr() As String
  If Not IsNull(varFolder) Then
    If Right(varFolder, 1) = "\" Then varFolder = Left(varFolder, Len(varFolder) - 1)
    arr = Split(varFolder, "\")
    SplitFolder = arr(UBound(arr))
  End If
End Function

Works for strings with both an "\" at the end or without one at the end.

 

[theDBguy]

Hi. Welcome to AWF!

Just another option, you could also try the following in your query.

LastFolder: Replace(Mid([PathField],InStrRev([PathField],"\",Len([PathField])-1)+1),"\","")

 

[arnelgp]

in short:

Public Function lastFolderFromPath$(ByVal p As String)
Dim v
v = Split(p, "\")
lastFolderFromPath = Trim$(v(UBound(v)))
If Len(lastFolderFromPath) = 0 Then
    lastFolderFromPath = Trim$(v(UBound(v) - 1))
End If
End Function

 

[Paul-Learning]

Thanks to you all for your help, it's much appreciated however I appear to have missed something out which, in retrospect, is blindingly obvious.

I went with theDBguy solution and it works perfectly, however, although I now have "Last Folder" I also need (as separate field) the first part of the original string eg titled below as "Path"

Original String: D:\Music\CD Collection\2000\Rock\
LastFolder: Rock
Path: D:\Music\CD Collection\2000\

Apologies for missing this part of the problem out

Thanks again
Paul

 

[arnelgp]

Public Function FolderFromPath$(ByVal p As String, Optional bolRemainingPath As Boolean = False)
Dim v, arr(), i As Integer
v = Split(p, "\")
FolderFromPath = Trim$(v(UBound(v)))
If Len(FolderFromPath) = 0 Then
    FolderFromPath = Trim$(v(UBound(v) - 1))
    If bolRemainingPath Then
        ReDim arr(UBound(v) - 2)
    End If
Else
    ReDim arr(UBound(v-1))
End If
If bolRemainingPath Then
    For i = 0 To UBound(arr)
        arr(i) = v(i)
    Next
    FolderFromPath = Replace$(Join(arr, "\") & "\", "\\", "\")
End If
End Function

LastFolder: FolderFromPath([fieldname], False)
Path: FolderFromPath([fieldname], True)

 

[Paul-Learning]

Thanks for your help arnelgp.

Pasted your code into a new module however one line is showing a problem when I go to compile:

Else
ReDim arr(UBound(v-1))

Any ideas?
Thanks
Paul

UPDATE: My knowledge of VBA isn't great but by breaking down the steps of what the code was doing I have slightly altered the above to now read:

Else
ReDim arr(UBound(v) - 1)

This now works. Thanks again everyone for you guidance

 

[arnelgp]

sorry, change it to:

ReDim arr(UBound(v)-1)

 

[theDBguy]

Thanks to you all for your help, it's much appreciated however I appear to have missed something out which, in retrospect, is blindingly obvious.

I went with theDBguy solution and it works perfectly, however, although I now have "Last Folder" I also need (as separate field) the first part of the original string eg titled below as "Path"

Original String: D:\Music\CD Collection\2000\Rock\
LastFolder: Rock
Path: D:\Music\CD Collection\2000\

Apologies for missing this part of the problem out

Thanks again
Paul

Hi Paul. I'm not in front of a computer now; but if you used my suggestion in a query, you could try this next:

Path: Left([PathField], InStrRev([PathField], "\" & [LastFolder]))

(untested)
Hope that helps...

 

[Paul-Learning]

Hi Paul. I'm not in front of a computer now; but if you used my suggestion in a query, you could try this next:

Path: Left([PathField], InStrRev([PathField], "\" & [LastFolder]))

(untested)
Hope that helps...

Hi, Appreciate yr feedback but problem now solved ... thanks a lot

 

[Gasman]

Hi, Appreciate yr feedback but problem now solved ... thanks a lot

Perhaps, but a much simpler solution?

 

[theDBguy]

Hi, Appreciate yr feedback but problem now solved ... thanks a lot

Good luck with your project.