Query language to give me the file name from the end of a path

[keun]

I have document paths, including file name, in a PATH field:

MyDocs\Pictures\honeymoonIsOVer.jpg
MyDocs\WorkStuff\Accounting\Quarterly\FakeInvoices.xls
MyDocs\HideFromTheBoss\NotPron\specs.jpg


So, the folder structure will vary, but the text I am looking for is everything after the last backslash. This gets me what I want in Excel:


=RIGHT(A2,LEN(A2)-FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,".","")))))

 

[keun]

Sorry, what's my problem... that function gets me the extension. Of course, I need the file name, including extension. My bad!

thanks all

 

[MajP]

Public Function getFileName(varPath As Variant) As String
  If Not IsNull(varPath) Then
    getFileName = Mid(varPath, InStrRev(varPath, "\") + 1)
  End If
End Function

SELECT tblOne.strPath, getFileName([strPath]) AS FileName
FROM tblOne;

[tt]
strPath FileName
MyDocs\WorkStuff\Accounting\Quarterly\FakeInvoices.xls FakeInvoices.xls
MyDocs\Pictures\honeymoonIsOVer.jpg honeymoonIsOVer.jpg
MyDocs\HideFromTheBoss\NotPron\specs.jpg specs.jpg
[/tt]

 

[keun]

Thanks. I used this last week, but took a while to get back here.