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.
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.
Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities.