Trim(right) until backslash is encountered

[scottb]

Hi everyone.
I have a two fields on a form, strImagePath and strImageTitle. strImagePath contains a file location path and strImageTitle is for the name of the file.

I am trying to build an OnExit event for strImagePath that will update strImageTitle with all of the characters in strImagePath from the right until the first \ is encountered.

So for example C:\Project Documents\Budget\Staffing\Resource Staffing Plan.xlsx would trim and put Resource Staffing Plan.xlsx into strImageTItle.

I am trying :
Private Sub strImagePath_Exit(Cancel As Integer)
strImageTitle = Trim(Right([strImagePath], (InStr(1, [strImagePath], "\"))
End Sub

But can’t get it to work.

Thank you for any help.
-Scott

[HansV]

Instead of the On Exit event, I'd use the After Update event - strImageTitle only needs to be updated when strImagePath has been changed.

Try this:

Code: Select all

Private Sub strImagePath_AfterUpdate()
    Dim arr() As String
    If IsNull(Me.strImagePath) Then
        Me.strImageTitle = Null
    Else
        arr = Split(Me.strImagePath, "\")
        Me.strImageTitle = arr(UBound(arr))
    End If
End Sub

[scottb]

Thanks Hans. AfterUpdate makes more sense. Works great. How does arr = Split(Me.strImagePath, " \") "know" to work from the right to the left ?

[HansV]

Split takes the value of strImagePath and splits it into parts based on "\" as delimiter. The result is an array where each part is an element.
For example, splitting "C:\Project Documents\Budget\Staffing\Resource Staffing Plan.xlsx" on "\" will result in an array consisting of 5 elements:

"C:", "Project Documents", "Budget", "Staffing", and "Resource Staffing Plan.xlsx".

arr(0) is the first element: "C:".
arr(1) is the second element: "Project Documents".
arr(UBound(arr)) returns the last element of the array, in this example "Resource Staffing Plan.xlsx".

[scottb]

Thank you for the explanation. Very good to learn about this. :-)

[Mark L]

Now that you've learned about Split(), you might also want to know about Join(). The does just the opposite, it builds a string from an array with a designated delimiter!

[scottb]

Very cool. Thank you Mark.