Use the InStrRev() OR strReverse() Function to Extract the Path from a File Name

Provided by:  FMS Development Team
 

Have you ever needed to extract just the path from a complete path and filename? Before Access 2000, finding the final backslash required either multiple lines of code to process each character in a loop or a single line that required mathematically subtracting the location found in the path from the length of the string (Access 97). Now, thanks to the wonderful new InStrRev() function, this mundane task can be completed in just one simple line of code.

Suppose:

strPathandFilename = "\\Server\Clients\data\test.xls"
strJustPath = Left(strPathandFilename, InStrRev(strPathandFilename, "\"))

Using the InStrRev() function with the Left() function, you can easily retrieve the text prior to the position of the last backslash without the math and without processing multiple functions.

Access 97 way:

strJustPath = Left$(strPathandFilename, _

Len(strPathandFilename) - InStr(StrReverse(strPathandFilename), "\")+1)

Using the strReverse() function inside of the InStr() function, you can easily retrieve the position of the last backslash. From there, subtract that position number from the total number of characters, and add 1. This will give you the length of the string needed for the Left$() to extract just the path.

Return to the tips page