|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Move THE To End of Title By Richard Rost 
6 years ago
Move "The" to the End of a Title In this Microsoft Access tutorial, I will show you how to move "The" to the end of a title. For example: "The Fellowship of the Ring" becomes "Fellowship of the Ring, The". MembersThere is no Extended Cut for this video. LinksLeft & Right Functions
IntroIn this video, I will show you how to move the word THE from the beginning to the end of a book title in Microsoft Access, such as turning "THE CAT in the Hat" into "CAT in the Hat, THE." We'll look at how to identify titles that start with THE, use functions like Left, Right, and IIF for text manipulation, use string concatenation in queries, and update all records at once with an update query. I'll also show you how to automate this process for new records using an After Update event with a simple Visual Basic procedure.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com.My name is Richard Rost, your instructor. In today's lesson, I'm going to show you how to move THE to the end of a title, such as THE CAT in the Hat. Today's question comes from Lauren. Lauren says, I have a table with book titles. How can I move THE to the end of the title? For example, THE CAT in the Hat should be CAT in the Hat, comma, space, THE. Lauren, we can do this with a little text manipulation, using a couple of different functions, like Left, Right, the Immediate If function (IIF), some string concatenation, and then of course if you want to change all of the records in your table, you can use an update query. Or if you want to process this for new records on the fly, you can use an After Update event. I'll show you a couple of different ways in this lesson. Here I have a real simple database. I made a table called BookT, and in BookT, we've got a book title, THE CAT in the Hat, THE FELLOWSHIP OF THE RING, and so on. Some of them have THE at the front, some of them don't, like Return of the King and Dragon Lance. Yes, as you can tell, I'm a nerd. I made a real simple form over here: a continuous form with all the records in it. We'll use this in a minute when we do our After Update event. The first thing I'm going to show you is if you've got a table like this with values already in it, you might want to change them all at the same time instead of having to go through and do them one at a time. So let's take a look at some different functions in a query. Let's go ahead and close this table and create a new query. Come up top here, pick Create, go to Query Design. All this stuff came in pretty big, let me shrink this down. I'm going to actually close the property sheet. Then for Add Tables over here, if this doesn't pop up for you automatically, click on the Add Tables button. Let's bring in BookT into our query, and then we can close this. So here's our basic query. I don't need the ID, so let's just bring down the Book Title. Now we can use the Left function to determine if this particular Book Title has THE on the left of it. Let's just make a new field called X. We'll rename it in a minute and I'll say Left of the Book Title, comma 4. Show me the left four characters of the Book Title. Why 4? Because we want that space in there. If it's just the word THE, it'll be 3, of course, but we want something that has 4. So hit Run and there you can see the left 4 characters. Now I want this to be equal to "THE " (with a space). Let's go back to Design View. In the next column, let's pick another value called Y. I'm just making these X and Y for demonstration purposes. Now I want to see the rest of the title, everything but the THE. What is that going to look like? It's going to be the Right of Book Title, but how many characters? Well, the length of the whole Book Title minus 4, just like that. In other words, I want the Right number of characters: the entire length of that string minus 4 characters. That's what it looks like. Now I've got the right part; in this case, CAT in the Hat. It's the length of this entire thing minus these 4 characters. I've got FELLOWSHIP OF THE RING, TWO TOWERS, and so on. Now, the ones that don't have THE in front of them are messed up, of course, but we're not going to touch those ones, so they're going to be just fine. Back to Design View. Let's make another column over here. Now we're going to use the IF function, Immediate IF (IIF). We're going to say: IF this is equal to THE space in it, then set the New Book Title equal to Y, comma, THE at the end of it. Otherwise, leave it alone. We'll call this one Z. IIF(X = "THE ", Y & ", THE", [Book Title]). We're going to take CAT in the Hat, comma, THE. Otherwise, just leave it Book Title. Now let's take a peek at it. There we go. That's the string we're looking for. The IF function says, take a look and see if this is "THE " (with a space). If it is, then make Z, the New Book Title, CAT in the Hat, comma, THE. Otherwise, if it's Return of the King, leave it as the original, Return of the King. Now I can run an update query to replace this with this. Unfortunately, you can't use this exactly the way it is now as an update query because you got these different fields in here, X, Y, and Z, which are going to be considered parameters if you make this an update query. So we can either save this and make a second query or we can replace this all with one long line, one long function. Here's what I'm going to do. I'm going to get rid of all this. I'm going to change this into an update query. Now notice, Book Title is now Update To. Now we just replace that whole thing that we just did with one long function. Fortunately, I put it on my clipboard. There it is. It's the same thing we just did in three columns, but I mashed it on the one column. It says, if the left four characters of Book Title equal THE with the space, then set Book Title equal to the right characters: Book Title, comma, then Book Title minus four, and otherwise just leave it Book Title. I like to break things down into multiple columns when I'm figuring it out or when I'm doing something for the first time, but then you can always re-simplify it and put it back together into one because Access sometimes doesn't like things broken up in multiple columns like that. If I run this now, nothing appears to happen because an update query happens silently. But if I look at BookT now, look at all my titles are fixed. I should have saved the backup on the table because now to do the other example I have to change the data back again. This works. I'll save this as my "Update THE" query. We'll use the same basic logic in my form to do an After Update event when this is typed in. How do I do that? Right-click, go to Design View. If you haven't taken any - if you don't know any Visual Basic whatsoever, go take my Developer 1 class, Access Developer 1. I teach all the stuff you need to know. If you're happy with just the update query, you can stop watching now because the rest of this involves some Visual Basic, but don't be scared. It's easy. It's only a couple of lines of code. The Book Title field: we want something to happen when this value is changed. Bring up the properties for Book Title. Here's the property sheet. Under the Events tab, find After Update right there. After Update: in other words, when I type in a new value and hit Enter or Tab, I want something to happen. Hit the ... (dot dot dot) little builder button there. Now you'll probably see a window pop up that says Choose Builder - pick the Code Builder. We're going to build Visual Basic code. The other options are Macro Builder and Expression Builder. We don't want those. We want the Code Builder. I have it turned off on my system. I'm in the developer classes now, normally. For my developer students, it's a pain to keep seeing that window pop up. You can turn it off in the Access options. Now this guy should appear. It says Microsoft Visual Basic for Applications. You put inside this thing here, Private Sub FirstName_AfterUpdate. This happens after the First Name field is updated. The first thing I want to do is see if the person typed in a value that begins with THE. So, if the left characters of FirstName, how many characters? Four, equals THE with a space, then do some stuff. End If. What kind of stuff am I doing? Well, I'm going to say FirstName equals the right of FirstName. How many characters? The length of FirstName minus four. And comma, THE. That's it. Otherwise, leave it alone. Save it. A couple lines of code. Three lines of code. Really, we could dwindle this down into one line of code if you really want to. I like to do If and End Ifs. Let's just get rid of that. If it's only one line of code, then you don't have to mess with it. It's one line of code literally. If the left of the FirstName four characters is THE, then FirstName equals the right that many characters and THE. Save it. Control-S. Close this window. I like to close the form and reopen it. Now when we type in a new value, "My memoirs." Nothing happened. "The Big Easy." I hit Tab and look at that: Big Easy, THE. "Sam's New Toy." Nothing happened. "The Little Giant." Boom. That's it. One line of code. I showed you how to do it with an update query if you want to change all of them. That's good if you've imported a whole bunch of titles and you want to fix a block of them. Or if you want to fix it as new records are entered, put it into the After Update event. That's it. Thanks for watching. I hope you learned something. There are lots of other lessons available on that website that cover the different topics that I talked about in today's video. The IF function, Left, Right, string concatenation, update queries, After Update events, basic Visual Basic stuff. I'll put some links in the video description below the window. Make sure you click on them and check out some of my other videos. If you're watching me on YouTube, make sure you subscribe to my channel, ring the bell, pick All, and you'll get email notifications whenever I release new classes. If you're watching on my website, make sure you subscribe. Hit the subscribe button to my Access Forum. You'll also get notifications when I release new videos there. If you haven't been on my website yet, go check it out. I have some pretty cool forums, lots of tip videos, templates, all kinds of stuff. If you have questions and you want to see them answered, maybe even in a video like this, go to my TechHelp page and post your questions there. Of course, you can always email me directly, but honestly, I prefer that you post them in the forums or on my TechHelp page. If you want to keep it private, if there's sensitive information, you can always send me a direct email. I'm more likely to answer questions in my forums or on the TechHelp page though. There's all my other cool stuff: my blog, Facebook, Twitter, YouTube. Now it's time for the shameless advertising portion of the video. If you like this, make sure you watch my Access Level 1 class. It's free. It's three hours long. Check it out. If you like that, Level 2 is just one dollar. That's another whole hour-long class for a buck. That's all. Hope you learned something. Thanks for watching. QuizQ1. What is the main goal demonstrated in this lesson?A. Sorting book titles alphabetically B. Moving "THE" from the start of a book title to the end with a comma C. Removing "THE" from all book titles D. Capitalizing all the words in a book title Q2. Which functions were used to manipulate the text in the query design? A. MID, CONCAT, IIF B. LEN, FIND, MAX C. LEFT, RIGHT, IIF, string concatenation D. UPPER, LOWER, REPLACE Q3. Why is the LEFT function used with 4 as the second parameter? A. To include "THE" and a space B. To ignore spaces after "THE" C. To capture only "THE" at the start D. To get the last four characters Q4. What does the RIGHT function do in the context of this tutorial? A. It adds "THE" to the title if missing B. It returns all of the title except for the first four characters C. It reverses the title D. It removes the last four characters Q5. What is the purpose of the IIF function in this lesson? A. To update records automatically when the database opens B. To check if the value starts with "AND" C. To conditionally change the Book Title if it begins with "THE " D. To split the text into uppercase and lowercase Q6. What should be done if the update query approach results in parameter prompts for fields like X, Y, and Z? A. Convert them into one long expression in a single column B. Use temporary tables to store values C. Ignore the prompts and proceed D. Rename the fields in the table Q7. When is it better to use the update query approach for this task? A. When processing single, new records B. When processing all existing records at once C. When updating records during form entry D. Never, because it is not effective Q8. How can the movement of "THE" be automated for new records entered via a form? A. By using a macro attached to the form's Open event B. By using the After Update event with Visual Basic code C. By manually running queries each time a record is entered D. By enabling auto-correct in Access Q9. What does the After Update event code do in the form? A. Checks if the Book Title field is empty B. Automatically capitalizes the book title C. Moves "THE" from the start to the end, if present D. Updates all titles in the table, regardless of entry Q10. According to the video, why does the instructor prefer to break down the logic into multiple columns while developing the query? A. Access always requires it to run B. It is easier to debug and understand before simplifying into one column C. It runs faster with multiple columns D. Users need to see all the intermediate results in their reports Q11. What advice did the instructor give for students who are unfamiliar with using Visual Basic in Access? A. Use only macros and avoid code B. Take his Access Developer 1 class for an introduction C. Hire a professional developer D. Use third-party add-ons instead of VBA Q12. What is the visible result when entering "THE Big Easy" in the form after implementing the After Update event code? A. "THE Big Easy" remains unchanged B. The title is changed to "Big Easy, THE" C. The title is removed entirely D. The title changes to all uppercase Answers: 1-B; 2-C; 3-A; 4-B; 5-C; 6-A; 7-B; 8-B; 9-C; 10-B; 11-B; 12-B 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. SummaryToday's video from Access Learning Zone covers how to move the word THE from the beginning of a book title to the end, separated by a comma and a space. For instance, turning "THE CAT in the Hat" into "CAT in the Hat, THE."Lauren asked how to handle this task in her book titles table. There are a couple ways to achieve this in Microsoft Access, depending on whether you want to update existing records all at once, or process new records as they are entered. I set up a simple table named BookT, which contains book titles. Some begin with THE, such as "THE CAT in the Hat" and "THE FELLOWSHIP OF THE RING," while others like "Return of the King" or "Dragon Lance" do not. To make this change for all records at once, you can use an update query with some basic string functions: Left, Right, Immediate If (IIF), and string concatenation. First, in a query, you can use the Left function to check if the title begins with "THE ". The four characters include the space that follows THE, so it is important to use four and not three. Then, you can use the Right function to capture the rest of the title, omitting those first four characters. By combining these with an IIF function, you can set up a new calculated field that checks if the title starts with THE, removes it, and places THE at the end of the title with a comma and a space. If the title does not begin with THE, it remains unchanged. Breaking this logic into separate fields helps with troubleshooting and understanding, but for the update query, it will need to be combined into one line because Access does not accept calculated fields (like X and Y) in an update query directly. After creating this consolidated expression, you can set up an update query to apply it directly to your Book Title field. Running this update query will change all existing records that start with THE, moving THE to the end as desired. Always make sure to have a backup of your table before running such an update, in case you want to revert your changes. If you'd prefer to make this adjustment automatically whenever a new record is entered or a title is updated, this can be handled in the form with a bit of Visual Basic for Applications (VBA). In the form's design view, open the properties for the Book Title field, select the After Update event, and use the Code Builder. Here, you want to add VBA code that checks if the title begins with "THE " (again, four characters including the space). If it does, the code should edit the title to move THE to the end of the string, separated by a comma and a space. If not, no changes are made. This process is simple and involves only a line or two of code. Every time a user edits or enters a title that starts with THE, the code will update it as needed. For other titles, nothing will change. In summary, you can handle this task for all existing titles with an update query or for new entries with a bit of VBA in the After Update event of your form. I have also covered using the Left, Right, and IIF functions, as well as setting up update queries and leveraging basic VBA to automate routine text manipulations. If you want full, detailed, step-by-step instructions for everything discussed here, you'll find a complete video tutorial on my website at the link below. Live long and prosper, my friends. Topic ListUsing the Left function to detect "THE" at the start of a titleExtracting the remainder of the title using the Right function Combining string functions to move "THE" to the end Using IIF to conditionally reformat the book title Creating a query to preview updated title formats Writing an Update Query to change all records in a table Combining multiple steps into a single Update Query expression Adding logic to a form's After Update event Using Visual Basic code to automate title formatting Conditionally reformatting titles on data entry in a form |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access move THE to end of title left right iif concatenate afterupdate moving the to end of the title PermaLink Move THE To End of Title in Microsoft Access |
