Microsoft Access Tutorial: String Functions (Left, Right, InStr, Trim)

Microsoft Access Tutorial: String Functions (Left, Right, InStr, Trim)

Views:88894|Rating:4.71|View Time:7:43Minutes|Likes:230|Dislikes:14
In this Microsoft Access tutorial, I’ll show you how to break up text fields into smaller parts using Left, Right, Trim, and InStr.

welcome to another tip of the week brought to you by access learning zone comm I am your instructor Richard Ross and today's lesson we're going to take a look at some string functions now I know I've covered the string functions in other tips of the week and in my other lessons however you can never learn too much about manipulating text when it comes to working with databases today we're gonna take a look at n string left right and trim this question comes to me from Scott Scott says he's got a text field called title in a table called master that is essentially some data with a little code in it alright the code is a left bracket followed by a number or letter followed by a right bracket and he says this code can appear anywhere throughout the field so it could appear in the middle at the beginning or at the end now if it was just at the end or just at the beginning that make this a whole lot easier but since it appears anywhere throughout well then we got some extra work to do so let's create a query query designer and I'll bring in my master table and I'll bring in title and what I want to do first is find the location of that bracket inside that string so I'll use the in string function I'll say bracket position I'll abbreviate it B rpoS colon is going to be equal to the in string function what am I looking for I'm looking in title the field title comma I'm looking for a quote left bracket quote that's what I'm looking for is a left bracket okay left bracket character all right there I am zoomed in a little bit a left bracket character let's run this query and there you can see in this one here the bracket position is the 11th character so one two three four five six seven eight nine ten eleven okay and bracket position over here is six so there's four five six that's the sixth character so now I know where I can get that value inside the title string now if this were a variable length code like if this at ABC inside of here then we to find the right bracket – but fortunately this is only one digit either a character or a number so we only have to worry about the finding the left one all right so back in the design mode there's the left bracket now that I know where the left bracket is I can pull that code out all right I can say the code is going to be in the middle somewhere in the middle of the string right middle of title comma the bracket position plus one I don't want the bracket itself so move over one and get that code comma one character all right let's see what that looks like run my query and there we go right find the bracket move one character over to the right that a and that's my code now Scott I'm assuming that you want to be able to pull this code out of this and be left with what's what the remainder of this is all right so let's grab the left side of that and the right side of that and we'll put those in two more fields over here back in design mode okay the left side LSD LSD I will call it the left string is going to be the left of the title comma a bracket position – one alright get everything to the left of the bracket position – one character cuz we don't the bracket itself and just in case we have any extra spaces I'll put the trim function around that that'll get rid of any extra spaces in front or behind it all right let's see what that looks like we're on it alright there's Joe Smith there's Bill sneak his bills to the left of that bracket there's nothing to the left of this bracket so that one's empty alright Dave Mason the whole thing and then just add them now we need the right side the right side is going to look like this our string is going to be the rights of title comma how long well the length of title – bracket position – – and I'll throw a trim around that whole thing as well trim to get rid of any extra spaces now let's run it first to make sure it works okay look at that great all right left string right string now let's see what we got why does this work well take a look at the data I need to write so many characters well the right four characters in this case alright how do I get to four well that bracket position is at position six all right so what I need to do is I need to say okay the length of title this whole thing minus the bracket position which brings us to here okay so that's position six so one two three four five six brings us to there from the right side if I need to less than that so that brings us there there is no right side here here the left bracket is position 1 so the right of the whole thing minus one the bracket position minus 2 more brings us there and the trim will take care of that extra little space same thing here the bracket position is in position 6 so there's 5 6 all right and then 2 less than that now what to do with this data once we've got it well we can reassemble this into a new field using an update query yes you could just update the title but I would recommend putting this into a new table if at all possible I like leaving my old tables alone because you never know when you might need the data in this format too so we could either add new fields to this master table or we could make a new table and drop them in there with an update query or an append query or we could just reassemble these and leave them and just work from this query there's a several different ways you can do it all right to reassemble that name and the one field wouldn't be that hard right new name or new title let's call it new title is going to be L string and our string we might need a space in there let me take a look yes we're gonna need a space in there sometimes sometimes not all the time all right sometimes let's see here what we get by dropping a space in there okay and it looks good all right now Sue's got a space in front of her right there let's see we got there and that is because the trim over here the whole thing is in our string so we're gonna want to trim this – all right the trims are important to get rid of all those extraneous spaces and that should work right give me the left strength force of space I think give me the right string and then trim the whole thing because this would have a space in the end of it – and Sue would have a space in the front of her so that should work just fine now again we can use an update query or an append query or just work with this data right here here's our code here's our new title we can throw those into new fields and I have separate lessons on how to do update and append queries if you want to see how those work thank you and I hope you found this tip helpful if you'd like to find more just like it go to my website at access learning zone dot-com and go to the tips section YouTube viewers make sure you go to access learning zone comm slash YouTube and you'll get a 50% discount on any of my full video tutorials

17 Replies to “Microsoft Access Tutorial: String Functions (Left, Right, InStr, Trim)”

  1. Me ontenz

    What if the code between the [ ] brackets are varied in length and what if they appear several times in a long text field? Example: [John Doe] is married to [Mary Smith]

  2. Rivera's Clean Cut Lawn Care

    could you explain how to do this when you have a right bracket and the text in between the brackets would be variable lengths. For example the text could sometimes be [abc1234] or [abc1234de]?

  3. Ashish Rai

    I have a string which contains data is following way

    1. AS_1334_ashish_rai
    2. Bups_ 1236_bhupendr_singh

    So there are numbers on different position in every string

    How can I pull numbers from this string in ms access ( this should come in numeric value not text)

    Please help.

  4. Mama Duck

    Hi Richard, I NEED a simple way to identify and replace recurring incorrect key words in each data field. I have compiled examples of 'dirty' text that can be fixed SAFELY. But I haven't been able to figure out a way to find and replace more than one word at a time. I have more than 250 key words that have to be corrected on a regular basis, and I have been doing it by hand for YEARS using field by field filters and typical find and replace all methods. I've tried a lot of different in  SQL string statements, always getting one error or another. I have an excel sheet where I did identify all the problem words. Fixing the problem on the data entry side of things is NOT and option.

  5. Javed Younas

    Nicely explained. But some of the area of video has some colored graph appears,Why is that ? (Explanation of Trim is great)

  6. Process-Geekoid

    Hello sir. I need your help in MS access. I have a huge table with thousands of rows. I need to filter out zipcodes that are 9 digits in length from the remaining zipcodes that are 5 digits in length. Can you help me with this?

  7. Clint Britt

    (access 2013)  How do I take the following imported text field : 94:30:25  and convert it to 94.51 Hours?  Thank you.  ps love the videos.  Clint

  8. PC Learning Zone - Computer Training

    That would require some complex logic. If the postal code is ALWAYS the last 8 digits, then you could work backwards from there, assuming the commas are always in the same spots… reliably.

  9. PC Learning Zone - Computer Training

    If you're counting the number of times "P" appears in a single string, you'd need some VBA with a loop and a counter variable. There isn't a single function that can do that. InStr can tell you the position of the FIRST instance of a character… but anything more would require some code.

  10. tariq

    I have a table in which there are several fields i want to have a field in that table which will calculate the how many times suppose a word "P" is entered in certain fields of the table

  11. hahahachiuh

    Hi the example is really clear but somehow, it is hard for me to do on my stuff. Can you show me how to apply this if the example is an address? For example I have this address "11 Salisbury St., Indooroopilly, QLD 4068" and "3 Milton Road, St. Lucia, QLD 4072" and I want to get the suburb only. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *