Solved Split String in Query based on last delimiter (1 Viewer)

Paul-Learning

New member
Local time
Today, 05:37
Joined
Nov 7, 2021
Messages
4
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

You've got your good things, and you've got mine.
Local time
Today, 01:37
Joined
May 21, 2018
Messages
8,463
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
Code:
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

I’m here to help
Staff member
Local time
Yesterday, 22:37
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Just another option, you could also try the following in your query.
Code:
LastFolder: Replace(Mid([PathField],InStrRev([PathField],"\",Len([PathField])-1)+1),"\","")
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:37
Joined
May 7, 2009
Messages
19,169
in short:
Code:
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

New member
Local time
Today, 05:37
Joined
Nov 7, 2021
Messages
4
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

..forever waiting... waiting for jellybean!
Local time
Today, 13:37
Joined
May 7, 2009
Messages
19,169
Code:
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

New member
Local time
Today, 05:37
Joined
Nov 7, 2021
Messages
4
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
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:37
Joined
May 7, 2009
Messages
19,169
sorry, change it to:

ReDim arr(UBound(v)-1)
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:37
Joined
Oct 29, 2018
Messages
21,358
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:
Code:
Path: Left([PathField], InStrRev([PathField], "\" & [LastFolder]))
(untested)
Hope that helps...
 

Paul-Learning

New member
Local time
Today, 05:37
Joined
Nov 7, 2021
Messages
4
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:
Code:
Path: Left([PathField], InStrRev([PathField], "\" & [LastFolder]))
(untested)
Hope that helps...
Hi, Appreciate yr feedback but problem now solved ... thanks a lot
 

Users who are viewing this thread

Top Bottom