[Dreambringer]

I did a quick search and was not able to find anything that I was looking for...

Here is what I have..

I am using Access 2003 and I have a table that tracks file names. I am looking to do a Find & Replace on anything before the "/".

For example: Find and replace you can use "*/" and replace with "". That will remove anything before the "/".

Ex: /My Documents/Downloads/Files/testing.xls
/Documents And Settings/Users/Desktop/TestFile/ReadOnly/testing.doc

So what I want to end up with is just:

testing.xls
testing.doc

I have tried Replace(), that only replaces the text and will not remove it, so I am not sure where to begin on this one.

Thank you in advance!



Edit:

The closest thing I have come up with is:
RTrim(Right([F1],InStr(2,[F1] & "-","-",1)-1))

But that does not stop at the "/" and sometimes it removes part of the file name...

[slurpee55]

Offhand, in a query this shows only the right data - but only for names the same length as "testing."
Expr1: IIf([name]<>"*/",Right([name],11),"Null")
Longer or shorter names would require you to change the value 11 to something else. I can't figure out how to find the last "/" in a string in Access....

[OBP]

This can be done in a Query using a VBA module or usinf VBA.
It might also be possible using slurpees example with the addition of a couple of extra columns, one would use the
Len([name]<>"*/")
or even include it in the IIF that would tell you how long the text was to the right of the last "/", that can then be used to subtract it from the Len([name]) to provide value that is currently 11.

Another alternative is to re-aquire the File data using File scripting and get the names from that.

[turbodante]

InStrRev will do it.

See query in attached.

[OBP]

Turbo, very nice where did you come across "InStrRev"?

[turbodante]

Originally Posted by OBP

Turbo, very nice where did you come across "InStrRev"?

There's a thing in Access2007 called Expression Builder where you can look up all available erm, expressions.

I was looking for the StrReverse equivalent that's available in Excel which does the same thing.

[slurpee55]

Very cool....

[Dreambringer]

Originally Posted by turbodante

InStrRev will do it.

See query in attached.

Thats great!

I was hope'n I would not have to VBA it, which I did last night, but this will work much better then what I came up with!

Below was my solution, but I will be using yours

        Set db = CurrentDb()
    Set rs = db.OpenRecordset("tbl_data", dbOpenDynaset)
    rs.MoveFirst
    Do While Not rs.EOF
        With rs
            i = InStr(1, !F1, "/", vbTextCompare)
            Do While i > 0
                j = InStr(i + 1, !F1, "/", vbTextCompare)
                If j > i Then
                    i = j
                Else
                    Exit Do
                End If
            Loop
            
            If i > 0 Then
                length = Len(!F1)
                s = Right(!F1, length - i)
                
                .Edit
                !F1 = s
                .Update
            End If
            .MoveNext
        End With
    Loop
    rs.Close