Extracting filename in a path

[decook]

We have over 500,000 file paths in a field. We need to extract just the file name (everything after the last"\") Can anyone think of a function we could use?

 

[Dugantrain]

I don't know your experience with VBA, but this function should do the trick:

Public Function fGet_Filename(strFilename As String) As String
Dim numLength As Integer
Dim i As Integer
numLength = 0
For i = 1 To Len(strFilename)
If Mid(strFilename, i, 1) = "\" Then
numLength = i
Else: numLength = numLength
End If
Next i
fGet_Filename = Right(strFilename, (Len(strFilename) - numLength))
End Function

You would call this code from a query. In your query's heading, write in something like:

Filename: fGet_Filename([Your_Filepath_Field])

of course, filling in 'your_filepath_field' with the field's name. The only problem with this function is that it will break if you have Null values in this field. To accomodate for Nulls, you'll need to handle it on your query side:

Filename: Iif([Your_Filepath_Field] Is Null,'No Filepath Given',fGet_Filename([Your_Filepath_Field]))

 

[Russell Hauf]

These functions should be a bit more efficient (faster). If you have Access 2000 or above, then the second one is the best bet:

Function GetFileFromPath(strFileName As String) As String
    Dim intPos As Integer
    Do While InStr(intPos + 1, strFileName, "\") > 0
        intPos = InStr(intPos + 1, strFileName, "\")
    Loop
    GetFileFromPath = Mid(strFileName, intPos + 1)
End Function

Function GetFileFromPath2K(strFileName As String) As String
    GetFileFromPath2K = Mid(strFileName, InStrRev(strFileName, "\") + 1)
End Function

Call them as explained above.

Hope this helps,

Russell

 

[decook]

Thank you both. Very helpful. The first one worked like a charm...and my user is v ery happy thanks to you

Kent