InStrRev
By Richard Rost
3 years ago
How to Use the InStrRev Function in MS Access
In this Microsoft Access tutorial I'm going to teach you how to use the InStrRev function which allows you to find the location of a particular string within a string starting from the end of the string. The opposite of the InStr function.
Prerequisites
Links
Recommended Courses
Usage
- ProfilePic: "c:\images\richard.jpg"
- LastSlash: InStrRev(ProfilePic,"\")
- FilenameOnly: Right(ProfilePic,Len(ProfilePic)-LastSlash)
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, instrrev function, Find Text From Right, what is the instrrev function, vba instrrev, string manipulation, last occurrence, specified substring, larger string, right to left, character
Subscribe to InStrRev
Get notifications when this page is updated
Intro In this video, I will show you how to use the InStrRev function in Microsoft Access to find the position of a character or substring starting from the end of a string. We'll look at a practical example of pulling just the file name from a full file path stored in a table, using InStrRev and the Right function in a query. I'll cover how InStrRev differs from InStr, and demonstrate step by step how to extract the information you need using these string functions in both queries and VBA.Transcript In today's video, I'm going to teach you how to use the InStrRev function. That's it. It stands for Instring Reverse in Microsoft Access. I'm going to show you how to find something inside a string from the end of the string.
This is both a beginner and developer video because it works both in regular queries and forms and stuff, and it also works in VBA. The same function works in two places.
Now, this function is directly related to the InStr function, which works the other way. If you want to find whether or not a particular string appears inside another string, use InStr and it finds the location starting from the left. It counts the number of positions from the left.
So go watch this video first if you're not familiar with InStr and it'll help you understand InStrRev better. Also, go watch my video on string functions. Left, Right, Mid. I talk about InStr in this one as well. We're going to also use the Right function in today's video. These are free, they are on my website, and they are on my YouTube channel. Go watch them.
Here I am in my TechHelp free template. This is a free database. You can download and copy it up from my website if you'd like to.
One thing that I hate about finding information online when you use the Google machine and pull up articles and stuff is you'll find a lot of references for how the InStr function works, or how the Left, etc., functions work. They give you really silly examples like, "Oh, if you want to find the first position of the letter I inside the word America." I like practical examples. I don't like doing a video unless I have a practical example.
This is one I came up with a little while ago. I had a student of mine who had a path and file name in their customer table to the person's profile picture. So let's add that real quick. Let's just add a field called ProfilePic, and that'll be Short Text.
In that field, she had stored the path and file name to where their picture was, and she wanted to be able to pull out just the file name part. Yes, I know for you advanced developer students who've taken my classes on the file IO system, there is a function already that can pull out the file name from a path. But if you just want a simple way to do that, we can look at a string like C:\\Pictures\\Richard.jpeg. All we have to do is say, "Okay, where is the position of the last backslash in that string?" To the right of that is going to be my file name.
Maybe D:\\Profiles\\Joe.jpeg, or Z:\\Server\\Database\\Pictures\\Tim.gif, or you might not have a backslash, it might just be "pic.jpeg". We'll see how that comes out.
All we want to find is where that last slash (that last backslash character) is. Save that. This is just one example. I've used InStr and InStrRev in a million different situations. In fact, I have a video coming up in a couple of days that's also going to use it, so I figured this is a good time to cover it for you. I didn't really spend a lot of time on InStrRev in my regular full courses because I spent a lot of time on all the rest of the string functions, but this one just doesn't get a lot of love. It's very handy.
Let's go make a query. Create Query Design and pull in the customer table, and let's bring in just that field. Take a peek, and there they are.
Let's not deal with all these null values, so we're going to set this down here as "Is Not Null". If you're not familiar with null, I have a video on it. Of course, I have a video on everything. I'll put a link down below. I'll also put a link to my images video. If you want to learn how to take this file and display the person's profile picture from that photo on the hard drive, then I cover that in my images video.
Now I have just these values. What I want to do next is use the InStrRev function. It's going to find the backslash character, the first backslash character starting from the right side of the string. That's what "reverse" means. Both InStr and InStrRev still return the position. It's not a count of characters from the right; it's still the count from the left. For example, this one is still going to return 1, 2, 3, 4, 5... whatever that is, 20 something. It's not going to return just eight. It's the count from the left, but it finds the one starting from the right. That's how the function works. People often get confused about that.
In the next column, I love this little feature I just discovered a little while ago. I'm going to zoom in so you can see it better. Shift+F2. We're going to find the position of the last slash. Actually, technically, the last backslash. Guns and Roses has nothing to do with this, so don't sue me.
InStrRev. The field is ProfilePic, comma, and then the backslash. Close it up. If I run it, there we go. There's the position of that last backslash character. Notice "pic.jpeg" has none, so it's a zero. That's okay. It still works.
Now we take the right number of characters off of that string after the backslash, and that'll give us the file name. Or the Left function will give us the path if you want the path only. You could do the extension too, but the extension, that depends, because you could have multiple things. If you find the last dot, that should give you the extension, like .gif, .jpeg, etc., if you want to display pictures differently.
Now we need the right characters. How many characters? We'll take 12 and subtract that from the entire length of the string, and that'll give you the file name.
Next column. Am I using this too much? That's a Windows feature, by the way. Just double tap the Control key. It works in Windows 11. Not sure if it works in 10 and earlier.
Shift+F2 to zoom in. Now we're going to say the file name only is going to be the right of ProfilePic, comma, how many characters, the length (covered that in the strings video), the length of ProfilePic minus last slash. So if the whole thing is 20 characters long and the last slash is 8, you'll get 20 minus 8, or give me the 12 rightmost characters.
Ready? Hit OK, and run it, and there's your file name. Isn't that pretty? That is a practical example for how to find the file name, a practical example for the InStrRev function.
I hate academic stuff. There you go. There's your TechHelp video for today about the InStrRev function. You'll see this coming up in a couple of days in another video that I have planned.
I hope you learned something today. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What does the InStrRev function do in Microsoft Access?
A. It finds the location of a character or substring starting from the end of a string but counting from the left
B. It reverses the entire string
C. It finds the number of times a substring appears in a string
D. It removes a specified substring from a string
Q2. What is the main difference between InStr and InStrRev?
A. InStr searches from the right; InStrRev searches from the left
B. InStr counts characters from the right; InStrRev counts from the right
C. InStr starts searching from the left; InStrRev starts from the right but both count from the left
D. InStr is only for numbers; InStrRev is only for text
Q3. Which scenario was given as a practical example for using InStrRev?
A. Finding the number of vowels in a sentence
B. Extracting the file name from a path string
C. Replacing all spaces with dashes in a string
D. Formatting phone numbers
Q4. If you use InStrRev to find "\\" in "C:\\Pictures\\Richard.jpeg", what will the function return?
A. The position of the first backslash from the left
B. The position of the last backslash from the left
C. The total number of backslashes in the string
D. The last character of the string
Q5. In the example query, what does a result of zero from InStrRev mean?
A. The string contains multiple backslashes
B. The string begins with a backslash
C. The string does not contain a backslash
D. The last character in the string is a backslash
Q6. How can you use the Right function in combination with InStrRev to extract a file name?
A. By taking the rightmost characters after the first backslash in the string
B. By taking the rightmost characters after the last backslash based on length minus position from InStrRev
C. By taking the leftmost characters before the first dot in the string
D. By repeating the file name twice
Q7. When using InStrRev, what does the returned position represent?
A. The position from the right side of the string
B. The ASCII value of the found character
C. The position from the left side of the string, starting from the right end for searching
D. The total number of characters after the found substring
Q8. What other string functions were mentioned as being related or useful in the video?
A. Split and Replace
B. Upper and Lower
C. Left, Right, Mid
D. Trim, Format, DateValue
Q9. Why is using InStrRev helpful when handling path and file name strings?
A. Because file extensions always start at the beginning of a string
B. Because file names can be anywhere and you need to find the last backslash to separate them
C. Because it always returns the number of spaces in a path
D. Because it can convert images into file names
Q10. What happens if the string you search with InStrRev does not contain the character you are searching for?
A. The function returns -1
B. The function deletes the string
C. The function returns 0
D. The function causes an error and stops execution
Answers: 1-A; 2-C; 3-B; 4-B; 5-C; 6-B; 7-C; 8-C; 9-B; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers how to use the InStrRev function in Microsoft Access. This function, short for Instring Reverse, is an essential tool for locating a specific character or string within another string, starting the search from the end and working backwards. It is useful for both beginners and more advanced developers, as you can use it in queries, forms, and directly in VBA code.
To understand InStrRev, it helps to be familiar with the InStr function, which searches for a substring from the left side, returning its position counted from the beginning of the string. If you're not already comfortable with InStr, I recommend reviewing my previous lessons on that function, as well as my videos on other string functions like Left, Right, and Mid. In today's lesson, we'll also make use of the Right function. These resources are available for free on my website and on my YouTube channel, so make sure you check them out if you need a refresher.
Let me give you a practical example to illustrate how InStrRev is helpful. I prefer real-world scenarios over textbook demonstrations like locating the letter 'I' in the word "America." For instance, one of my students had stored the path and file name of profile pictures in her customer table, and she wanted to extract just the file name portion. While there are more advanced solutions for pulling file names out of a file path, particularly if you've taken my classes on the file I/O system, today I'll show you a simple technique suitable for everyday use.
Suppose you have a string like C:\\Pictures\\Richard.jpeg. Our goal is to find the position of the last backslash in the path. Everything following that backslash is the file name we want. You might see similar examples with D:\\Profiles\\Joe.jpeg, or Z:\\Server\\Database\\Pictures\\Tim.gif, or there might be just a file name, like pic.jpeg, with no backslashes at all. Regardless, locating the final backslash character allows us to cleanly separate the file name from the full path.
To walk through this process, let's set up a query in Access using a table with a field named ProfilePic that holds the path and file name. To avoid confusion with empty values, it's best to filter out nulls, which you can do with an "Is Not Null" condition in your query criteria. If you need more background on handling null values in Access, I have a tutorial covering that topic as well.
With the filtered results, we apply the InStrRev function to find the position of the last backslash in each string. Keep in mind that InStrRev returns the position counted from the left side, not the right; it simply starts searching from the end and works its way forward. For a string like "pic.jpeg" that contains no backslashes, the function returns zero, which is fine for our purposes.
Once we've identified the position of the last backslash, the next step is to extract the file name itself. This is where we use the Right function. We take the total length of the ProfilePic string, subtract the position of the last backslash, and use the result as the number of characters we want from the right end of the string. For example, if the whole string is 20 characters and the last backslash is at position 8, subtracting gives us 12, and we extract the last 12 characters, which is our file name.
This method also adapts easily if you want to do something like extract just the file extension. In that case, you'd look for the last period in the string and apply the same logic.
What I appreciate about this approach is that it's a straightforward, practical solution to a real-world problem, avoiding overly academic scenarios. I wanted to show you how to put InStrRev to work in a way that's immediately useful in your own projects.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Using InStrRev to find the last occurrence of a character in a string
Applying InStrRev in Access queries
Extracting a file name from a full path string
Handling strings with and without the delimiter
Using the Right function to retrieve text after a specific character
Combining Len and InStrRev to extract file names
Using Is Not Null criteria in a query to filter records
Differences between InStr and InStrRev functions |
