InStr, Right, etc. . .

[kidzmom3]

I've been trying to develop an expression that will give me just my file name at the end of a path, i.e.

C:\MyDocuments\MyPictures\ERFG4567.jpg

Where I just want to get the ERG4567.jpg. I have 2 variables with trying to work this out. One is I have no idea what the path will be, so there could be anywhere from 2 to ??, "\" marks, so the InStr hasn't been very effective. Also, the file name length can vary also, so using the "." in my InStr is also not very effective. Any suggestions??

 

[Mile-O]

If you have A2000 or above I believe you can use the InStrRev function.

If not then:

Public Function Reverse(ByVal strPath As String) As String

    Dim strTemp As String, intCounter As Integer
    For intCounter = Len(strPath) To 1 Step -1
        strTemp = strTemp & Mid(strPath, intCounter, 1)
    Next intCounter
    
    Reverse = strTemp
    
End Function

Public Function GetFile(ByVal strPath As String) As String
    Dim intPos As Integer
    
    strPath = Reverse(strPath)
    
    intPos = InStr(1, strPath, "\")
    
    GetFile = Reverse(Left(strPath, intPos - 1))
    
End Function

Call GetFile

 

[dcx693]

Try this function:

Public Function GetFileName(strFullname As String)

  Do Until (InStr(strFullname, "\")) = 0
    strFullname = Right(strFullname, Len(strFullname) - (InStr(strFullname, "\")))
  Loop

  GetFileName = strFullname

End Function

which I adapted from code posted by Daxton A.

 

[kidzmom3]

This looks like it would work, but how do I tie it into the expression builder in an updte table query?

 

[kidzmom3]

Stupid is as stupid does!! I got the function called in the expression -- after I refilled my coffee cup!!!! Thanks for the code help.
By the way InStrRev is not in A2000, I'll have to check that out in A2002.

 

[raskew]

In Access 97 you have to 'roll your own'. No reason to believe that this won't work with subsequent versions.

Function xLastInStr(ByVal tstr As String, twhat As String) As Integer
'*******************************************
'Name:      xLastInStr (Function)
'Purpose:   Return location of last instance of a character or phrase.
'Inputs:    Call xLastInStr("the quick brown fox jumped the lazy dog", "the")
'Output:    28 - Location of last occurence of "the"
'*******************************************

Dim i As Integer, n As Integer, tLen As Integer

n = 0
tLen = Len(twhat)
For i = Len(RTrim(tstr)) To 1 Step -1

  If Mid(tstr, i, tLen) = twhat Then
      n = i
      Exit For
  End If
Next i

xLastInStr = n

End Function

To test, from the debug window:
x = "C:\MyDocuments\MyPictures\ERFG4567.jpg"
? mid(x, xlastinstr(x, "\")+1)
ERFG4567.jpg

HPH-

Bob

 

[pono1]

A little rough (you would want to error check the passed-in string for a backslash) but it's good to have options.

Function GetFileName(Text as String) as String

'Get string passed in.
	Dim strPath as String
	strPath = Text

'Create a dynamic array.
	Dim strParts() As String

'Split the string into parts,
'using the backslash as a delimiter,
'filling the array's elements with the
'broken up string segments.
	strParts = Split(strPath, "\")

'Test.
	MsgBox "The file name is " & strParts(UBound(strParts))
	MsgBox "The drive letter is " & strParts(LBound(strParts))

'Return file name.
	GetFileName = strParts(UBound(strParts))

End Function

Regards,
Tim