Access string extraction 2

[swtrader]

I need to extract the file name from a "path" in a query --

Ex: Path is... C:\DATA\Reports\MonthlyActivity.xls

Need only: MonthlyActivity

The path link may vary depending on how many subfolders there are.

 

[JBinQLD]

amazingly there is a reverse string functtion in vba. Dont have it installed here but look for revstr, revstring, etc.

Perform that and then search for \ (pos=instr(MyString,"\"))

now flip the string back again (RevStr)

Extract right "pos" most chars (strResult=right(MyString,pos)

I reckon something like that would be the go,

JB

 

[PHV]

A starting point:
strPath="C:\DATA\Reports\MonthlyActivity.xls"
MsgBox strPath & vbCrLf & Mid(strPath, 1+InStrRev(strPath,"\"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[swtrader]

Used 3 steps to get what I needed using your suggestions:

1. Expr1: Mid([DocHyperLink],1+InStrRev([DocHyperLink],"\"))

2. Expr2: InStrRev([Expr1],".")

3. Expr3: Left([Expr1],([Expr2]-1))

Can probably combine those but what I have works for now.

Wasn't aware of InStrRev.

Thanks to each of you!





swtrader
-- If you don't know where you're going, you'll always know when you're not there.