Anything similar to Excel FIND() ?

[TimE]

I am trying to accomplish in access VBA (or update query?) the same result as using the Excel FIND().

Excel: In Cell A1, I have a network location that I need to get the last folder (last folder name will vary in length)

Formula in B1:
=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

Example
A B
1 \\server\share\docs\pdf PDF


The piece of code that I am using in access to insert the name of folders (and subfolders) is:

DoCmd.RunSQL ("Insert Into tblFiles Values('" & objFiles.Name & "','" & objFolder.Path & "'," & Round(objFiles.Size / 1024, 2) & ",[B][COLOR="Red"]""""[/COLOR][/B]);")

The """" is where I want to use the last folder from objFolder.Path

So the objective of the above code would give me PDF in the corresponding filed in the tblFiles table.

If this is not possible using VBA, would an update query on the final table be the way to go?

 

[pbaldy]

It sounds like it does something similar to InStr() or InStrRev(). See if those do what you want.

 

[HiTechCoach]

I agree with Paul.

Here is how I would do it in Access VBA:

? Mid("\\server\share\docs\pdf", InStrREV("\\server\share\docs\pdf", "\")+1)
pdf