Here's a generic file path parser that I picked up from somewhere:

Function ParsePathElement(ByVal strFullPath As String, _
   strElement As String) As String

   ' Returns the required section of the specified Path
   '
   ' In:           strFullPath - a string from which the specified
   '               Path element is to be extracted
(Drive,Dir,DriveDir,Drive &
   '               Dir,File,Ext,FileExt)
   ' Out:          a string of the specified Path element
   ' Usage:        ?ParsePathElement("C:\Win95B\readme.txt", "Drive")

   Dim Drive As String
   Dim Dir As String
   Dim File As String
   Dim Ext As String

   ' Parses FullPath and returns required element

   Dim i As Integer, f As String, Found As Integer
     Drive = left$(CurDir, 2)   ' Current drive if none explicitly
specified
     Dir = ""
     File = ""
     Ext = ""
     strFullPath = Trim$(strFullPath)
   '
   ' Get drive letter
   '
     If Mid$(strFullPath, 2, 1) = ":" Then
       Drive = left$(strFullPath, 2)
       strFullPath = Mid$(strFullPath, 3)
     End If
   '
   ' Get directory name
   '
     f = ""
     Found = False
     For i = Len(strFullPath) To 1 Step -1
       If Mid$(strFullPath, i, 1) = "\" Then
         f = Mid$(strFullPath, i + 1)
         Dir = left$(strFullPath, i)
         Found = True
         Exit For
       End If
     Next i
     If Not Found Then
       f = strFullPath
     End If
   '
   ' Add current directory of selected drive if absolute directory not
specified
   '
     If Dir = "" Or left$(Dir, 1) <> "\" Then
       Dir = Mid$(CurDir(left$(Drive, 1)), 3) & "\" & Dir
     End If
   '
   ' Get File name and extension
   '
     If f = "." Or f = ".." Then
       File = f
     Else
       i = InStr(f, ".")
       If i > 0 Then
         File = left$(f, i - 1)
         Ext = Mid$(f, i)
       Else
         File = f
       End If
     End If

     Select Case strElement
       Case "Drive"
           ParsePathElement = Drive
       Case "Dir"
           ParsePathElement = Dir
       Case "DriveDir"
           ParsePathElement = Drive & Dir
       Case "File"
           ParsePathElement = File
       Case "Ext"
           ParsePathElement = Ext
       Case "FileExt"
           ParsePathElement = File & Ext
     End Select
End Function

Hi, Sam.

There are a number of ways to separate the file name from the rest of the
path in a text string.  Here's one of the simplest if you are using Access 2K
or newer:

' * * * * Start Code * * * *

Public Function getFileName(sPathAndFileName As String) As String

   Dim sArray() As String
   Dim idx As Long
   
   If (Nz(sPathAndFileName, "") <> "") Then
       sArray() = Split(sPathAndFileName, "\", -1, vbTextCompare)
       idx = UBound(sArray)
       getFileName = sArray(idx)
   End If
   
   Exit Function
   
ErrHandler:

   MsgBox "Error in getFileName( )." & vbCrLf & vbCrLf & _
       "Error #" & Err.Number & vbCrLf & Err.Description
   Err.Clear
   getFileName = sPathAndFileName

End Function        '  getFileName( )

' * * * * End Code * * * *

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions.  Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

This is hard coded but should give you an idea

Public Function parsefilename()
Dim filename, results
results = Array(4)

filename = "C:/data/Filename.xls"
filename = Replace(filename, ":", "") ' not needed if you want to see "C:"
filename = Replace(filename, ".", "/")
results = Split(filename, "/")
MsgBox results(0) & "  " & results(1) & "  " & results(2) & "  " &
results(3)
End Function

This obviously could be more general by using a larger array size.
Less obviious but more valuable in the long run if you need it a lot is to
build a function that would count the delimiters then set the array to the
count plus one.

Ideas compliments of Pick Basic, where the Replace command is *MUCH* more
powerful.