Access Query Specific Character String Select

[DJohn22]

I have two tables in my database. TableA has a field that contains
filenames only. TableB has a field that contains the full path and
filename. In my query, I would like to create an expression that
extracts the filename from the field in TableB and compare it to the
filename field in TableA and show only those with matches.
I am sure it can be done using an InStr function with the Right
function. What I do not know how to do is to tell it to go right until
it hits the first backslash (\).

Can anyone help?

 

[Guest]

You can return the file name from the full path using a combination of the
Mid$ and InstrRev functions:

Mid$(strPath,InstrRev(strpath,"\")+1)

The Dir function will also do it, but that's inefficient as it involves a
disk access each time.

Ken Sheridan
Stafford, England