How to Use the Mid Function to Get the Right Side of a String in Microsoft Access

 How to Use the Mid Function to Get the Right Side of a String in Microsoft Access

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, we got one for the beginners, and it's surprisingly something I just learned myself. And that's how you can use the mid-string function, just like the right function, which totally blew my mind when I saw this a couple of days ago. Let me give you some background.

Okay, I've been working with string functions since I was a child, literally. I started programming in BASIC on my Cocoa, my Tandy Radio Shack Color Computer, back in the 80s. And I've been using left, right, mid, length, and in-string since I can remember. I've always used left to get the left X number of characters from a string; right to get the right X most number of characters from a string; mid to get stuff in the middle (you go in three characters and give me the next three characters in the middle of the string); length to go into the whole length of the string, and in-string to find the position of something inside that string. All right? And that's pretty much it. Go watch this video for more details on how all of these guys work.

Well, what I didn't realize was it's actually easier to get the right side of the string using the mid function than it is to use the right function. Now, let me show you.

All right, let's say you got a table or you import some data, whatever, where you've got a full name in one field and you want to separate them. Okay? Now, obviously, if you've got a string where you know the position of where you want to separate it, like it's always the fifth character, then yeah, it's easy to use the right string function to get the right four characters. But if it's different, if it varies like here, then it's actually easier to use mid. Let me show you what I mean.

So let's create a query, and this query will be based on that full name table. I'll bring in the full name. And if you look at it, now we've got to figure out where in that full name the space is. And yes, this is a very simple example. It only works with names that have one space in them. If you get a name like Jean-Luc Picard or Eddie Van Halen, it's not going to work. Just bear with me. I'm just trying to make a point. Okay?

Now, to find that space, we can use the in-string function. So, the space position is going to be in-string, the full name field, comma, and then a space like that. That'll say, give me the position of where that space character is. And if I run this now, there you go. In my name, it's in the eighth position. Down here, it's in the fourth position and so on.

Now, to pull out the left X characters of the first name, we just grab the left X minus one characters. So in here, to find the first name, we'd go first name is the left of full name, comma, space position minus one. And now if I run this, there you go, there's the first name, okay?

Now, traditionally, what I used to do to get the last name is you'd have to figure out the length of the entire string and then subtract the space position. So it used to look like this: The last name would be the right of full name, comma, the length of full name minus space position. That'll figure out the entire length of the string and then subtract that space position, and that will give you the right number of characters. And that works. That's what I've been doing for years.

But it's actually easier. All you've got to do is say, give me the right X characters from the space position and add one to it. Watch, it's really, it's weird. Watch. We'll do last name two over here. Watch. Oh, someone's beaming in. All right, we'll say last name two. It's going to be mid full name, comma, space position. That's the space. Plus one. 

Now, normally, I always thought since I was a child, I always thought you had to have that third parameter. How many over do you want to go? But if you don't specify that, it just goes to the end of the string. Hit OK and watch this. Mind blown. It basically says, start at that character, that space position character, and if you don't specify the next parameter, it just says, give me from that spot to the end of the line, the end of the string.

I saw this. I'm like, what is going on? This goes against everything I've ever known about mid, and some of you probably know this already. I did not. So, I immediately had to make a video and share this with all of you guys. I'm 51 years old, I've been programming in BASIC since I was probably eight years old, and I just learned this. So, don't feel bad because even I still learn the simplest things that just are like, what? That blew my mind.

So if you already knew that, I want to hear from you. Just comment down below and tell me. Say, Rick, what, have you been sleeping for these 30-some years?

OK, well, that's it. That's all. I've got lots more lessons like this on my website. Come check it out. You'll find links down below. That's going to be your TechHelp video for today. Hope you learned something. I know I did. Live long and prosper, my friends. I'll see you next time.

A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.

For a complete video tutorial on this topic, please visit https://599cd.com/MidRight