split text problem - brain freezing up

[Davros]

hi guys
i think its a simple problem and i'm pretty sure it involes the MID, LEN, INSTR functions - but can I figure it out?? NO. my brain is turning to jelly.
basically i have thousands of photographs {linked not embedded} in the format

c:\Cemeteries\Cemetery Pictures\New Quay Cemetery Pictures\New Quay - B - 089\New Quay - B - 089 - 001.jpg

what i need to do is to strip everything out so that i am left with everything to the right of the last "\"

New Quay - B - 089 - 001.jpg

the length of the text field varies so i always need to find the last "\" reading from left to right (or the first"\" reading from right to left, whatevers easiest)

any ideas guys??

thanks

 

[raskew]

Hi -

Lookup the InStrRev() function. Using this in conjunction with the Mid() function should give you what you need.

On the offchance that you are using Access97, here's a 'roll-your-own' version.

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

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

    n = 0
    tLen = Len(pItem)
    For i = Len(RTrim(pstr)) To 1 Step -1
      If Mid(pstr, i, tLen) = pItem Then
          n = i
          Exit For
      End If
    Next i

    InStrRev97 = n

End Function

HTH - Bpb

 

[Tim L]

According to the VBA Editor Help file, INSTR

Returns a Variant (Long) specifying the position of the first occurrence of one string within another.

and takes the form:

InStr([start, ]string1, string2[, compare])

or, in easier to read terms of your requirement:

InStr(intStartPoint, strFileName, "\")

Where intStartPoint is an integer specifying the start point to search and strFileName is the string you are searching.

Because InStr counts from the beginning of a string and there is more than one occurrence of a "\" in the search string you will need to work your way through strFileName until you have found the last occurence, which is identified by the function returning a 0.

Procedurally you will need to record the highest occurrence position after each search through providing the result is not 0, as the final search will set the returned value to 0. So, something like this:

Set start point to beginning of searched string.

Start a loop of some sort (to your personal preference)

Search the string, if the value returned by InStr is not 0 then place this value in intHighestPosition (for example).
If InStr returned 0 exit the loop
Set the start point to intHighestPosition -> back to start of loop.

Loop Exited

intHighestPosition should contain the value of the furthest right position.

HTH

Tim

 

[missinglinq]

Where FileNam is your desired file name and CompPath is the complete path:

FileNam = Right(CompPath, (Len(CompPath) - InStrRev(CompPath, "\")))

 

[Davros]

thank you guys
I was nearly there

cheers