|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Replace Function By Richard Rost 
6 years ago
Use the Replace Function to Fix Phone Numbers In today's lesson, I'm going to show you how to use the REPLACE function in an Update Query to change data in your tables. Specifically, we have a table with phone numbers in a bunch of random formats like: 716-555-4444, (716)999-3333, 239.444.3333, and so on. This lesson will show you how to make them all uniform like 2395551212. MembersThere is no Extended Cut for this video. Links
IntroIn this video, we'll talk about how to use the Replace function in Microsoft Access to clean up fields such as phone numbers by removing unwanted characters like parentheses, dashes, and dots. We'll walk through building a query to strip out these characters, show how to create an update query to apply the changes to your data, and discuss best practices for cleaning imported data before adding it to your main tables.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today's question deals with the Replace function, which is a function in Access you can use either in your queries, your forms, or in your VBA code to replace one string of characters with another one.Today's question comes from Barbara, and she says, I have a customer table that has all kinds of mixed-up data in it. For example, in the phone number field, some people entered (716)333-5555, whereas other people enter it with just dashes and no parentheses. All I want are the digits without other characters. Is there an easy way to strip them out? This is something I have to do on a regular basis and find and replace is a pain. Well, Barbara, this is a perfect use of the Replace function, where you can use it to replace one character or string of characters with another one, or a blank string in that case. Couple that together with an update query, and it's very easy to remove all the characters that you don't want from something like a phone number. Let me show you. Here I've got a real simple customer table: first name, last name, address, and phone number. You can see the people that entered data in this phone number field all mixed up. Some have parentheses, some have dashes, some have dots. I want to replace these characters with just a blank string, to get rid of them. You could do this on a regular basis with a find and replace, but that's a pain. Be nice if you had one little query you could click on and run, and it would just make the change for you. Especially if you import data from another company. I get a lot of people that tell me that they have data they pull in from another company, marketing information, customers, and they want to fix it before they put it in their main customer table. That's what I recommend doing. Import this into a secondary table, make your changes with an update query or whatever, and then put that data that's clean in your main customer table. But let's see how we can get rid of those characters. We're going to use a query. First, we'll just make a basic select query. Create query design. Add the customer table. Let's find the phone number field. If I look at it now, I've just got the basic list of phone numbers. The first thing I'm going to do is go one at a time and replace the characters that we don't want. Let's look for that open parenthesis. We're going to use the Replace function. We'll call this one P2, phone number 2, colon. We're going to set that equal to Replace. The Replace function takes three bits of information. The first parameter is the field that you're pulling in. So phone, comma. The next thing is what string are you looking for? Let's look for an open parenthesis. It's got to be inside quotes: open parenthesis, then close quote, comma. Now, what are you replacing it with? If I wanted to replace it with a letter X, I could put an X in there. But I just want a blank string there. In other words, replace it with nothing. Close that up and then press enter. I'll zoom in a little bit so you can see what it looks like. P2: Replace. Inside parenthesis, phone. Access puts the brackets around phone because it's a field name. Then, comma, quote, open parenthesis, quote, comma. Then two quotes again, and then close parenthesis. Three parameters there. If I run this now, see what I got? It replaced that first open parenthesis with a blank, so it basically got rid of it. Now I'll do the same thing for the close parenthesis. Let's go back to design view. We'll make another field. You can't do multiple replacements in the same step, but you can make multiple calculations in the same query. I'm going to say P3 is going to be Replace. I'm going to replace in P2 now, take what this gave me, and make another replacement in it. Comma, find the close parenthesis, and then replace it with a blank. If I run it now, you can see both parentheses are gone in P3. Let's do the same thing one more time for the dash and for the dot. P4 is Replace in P3, comma, the dash, and then blank. One more time with P5. Replace from P4 the dot with a blank. Now if I run it, you can see my final column P5 there is nice and clean. All the characters are gone. If you've got other stuff in here like spaces, you can remove those too. Same technique. This is the column that's got our final value in it. Let's save this query. I'll call this my Clean Phone Q. It cleans the phone number up. You can run it right here and you see the same results. Now what I want to do is simply make an update query that copies this into the original field. You can't easily do it inside of the same query. So we'll make another query that just simply runs this one, pulls this value, and puts it in this field. This is the beauty of nested queries. Create another query. Create a design. We're going to pull in our values from Clean Phone Q. Change this into an update query. The phone number field is going to be updated to P5. Now, if you just hit enter sometimes, it will put it inside of quotes like that. We don't want the actual word P5. We want the field value P5. So put it inside square brackets. That's important. If I run this query, nothing appears to happen because it's an update query. But if you look in your customer table, everything is clean. The update query ran, it cleaned up the phone numbers, and it put it back inside the phone number field. I'm going to save this one as well. Let's call this Clean Phone Update Q. That's the one you'll run. Every time you import new data, you run Clean Phone Update and it will just fix your phone numbers. Of course, like I tell everyone, make sure you back up your data before running update queries. You should have a nightly backup on your system anyway, to back up tables or back up the entire database file. I get emails all the time from people who say, I ran this update query and then it messed things up. Restore your backup. Before you run any update queries, back up your data. I've got lots of lessons on my website on how to back up your data. I'll put some links below. That's pretty much it. I cover the Replace function in a couple of my lessons: Access Expert 14 and 25. I also have lots of lessons on append and update queries, which you'll find on my website. Again, I'll put links below. If you have any questions, visit the TechHelp page. You can find it right there on my website. I'll put a link below. Make sure you subscribe to my channel or ring the bell so you get notifications when I post new videos like this one. I've got pretty active message forums on my website. There's a link. You can join my Facebook group. There's all the fancy stuff: my blog, Facebook, Twitter, YouTube. Of course, the advertising portion of the lesson: you can watch my Level 1 Access for free. It's a three-hour tutorial. There's a link. If you like that, Level 2 is just one dollar. Thanks a lot. We'll see you next time. QuizQ1. What is the main purpose of the Replace function in Microsoft Access?A. To replace one string of characters with another or with a blank string B. To create new tables automatically C. To back up the entire database file D. To merge multiple tables together Q2. In the scenario described, what was Barbara trying to achieve with her customer table's phone number field? A. Add parentheses and dashes to all phone numbers B. Remove unnecessary characters to keep only digits C. Sort the phone numbers alphabetically D. Count the total number of phone numbers Q3. Why is using the Replace function with an update query better than using find and replace manually for cleaning data? A. It can be done with just one click and is repeatable B. It sorts the data faster than find and replace C. It does not require backing up any data D. It only works for text fields larger than 50 characters Q4. When setting up a query to clean up the phone numbers, what is the first step demonstrated in the video? A. Create a new table for clean phone numbers B. Create a select query and add the phone number field C. Run a report on the customer data D. Write a macro to automate the import process Q5. What is the correct syntax for replacing an open parenthesis ( with a blank using the Replace function in a query? A. Replace([Phone], "(", ")") B. Replace([Phone], "(", "") C. Replace("(", [Phone], "") D. Replace([Phone], ")", "") Q6. If multiple characters need to be removed from a string, how can this be accomplished in one query? A. Use multiple Replace calculations in the same query, each referencing the previous calculation B. Only one Replace can be used in a query C. It is necessary to write VBA code for each character D. Use the Replace function outside of Access Q7. After cleaning the phone numbers in a select query, how do you update the original phone number field in the customer table? A. Run a find and replace manually B. Use an update query referencing the cleaned value C. Export and re-import the table D. Delete all records and re-enter the information Q8. Why does the instructor recommend backing up your data before running update queries? A. In case something goes wrong, you can restore your data B. Backups make update queries run faster C. Access will refuse to run update queries without a backup D. Update queries cannot be undone by any means Q9. How can you ensure that your update query is updating the field with the value from your cleaned query and not just the text "P5"? A. Place P5 inside square brackets in the Update To row B. Place P5 inside parentheses C. Type P5 without any brackets or quotes D. Add P5 as a new field in the table Q10. What should you do every time you import new data that may require cleaning? A. Run the Clean Phone Update query to fix the phone numbers B. Manually check each record for accuracy C. Only run the update query once a year D. Delete all imported data before importing again Answers: 1-A; 2-B; 3-A; 4-B; 5-B; 6-A; 7-B; 8-A; 9-A; 10-A 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 focuses on how to use the Replace function in Microsoft Access to clean up inconsistent data, particularly in fields like phone numbers. Often, when importing customer data, you may encounter inconsistencies in how phone numbers are formatted. Some records might include parentheses, others may have dashes, dots, or even spaces, making it hard to maintain uniformity. Manually using Find and Replace each time is not efficient, especially when you handle new data imports regularly.Let me walk you through a simple method for cleaning up phone number entries using the Replace function in conjunction with update queries. The Replace function in Access allows you to substitute one character or group of characters in a string with another, or with nothing at all. This is particularly handy for stripping out unwanted characters from phone numbers so only the digits remain. Suppose you have a customer table with a phone field that displays inconsistent formatting. The goal is to remove unnecessary characters, such as parentheses, dashes, and dots, so you're left with just the numbers. To start, you can create a select query based on your customer table, focusing on the phone field. Using the Replace function, target one character at a time. For example, begin by removing the open parenthesis, then follow up with the close parenthesis, the dash, and the dot, building multiple calculated fields in your query. Each calculation builds on the previous one, gradually stripping away the unwanted characters. You can also use this technique to remove spaces or any other extraneous characters as needed. Once you have the desired result in the last calculated field of your select query, save this query as something like "Clean Phone Q". Now, to actually update the existing phone numbers in your customer table, create a new update query. Use the cleaned values from your earlier query to overwrite the original phone number field in the table. It's important to make sure you reference the calculated field correctly using square brackets to indicate it's a field name, not a literal value. When you run the update query, it processes all your records, cleaning up the phone numbers in the table at once. Remember, before running any update queries, always back up your data. Making regular backups ensures that if anything goes wrong, you can restore your information without hassle. I recommend backing up your database or at least the relevant tables before making mass changes. If you have to clean new imported data often, run the update query each time to maintain consistency in your database. I cover the Replace function and related update queries more thoroughly in my Access Expert 14 and 25 lessons, and there are also plenty of resources on append and update queries available on my website. If any questions come up while you're working through these steps, don't hesitate to visit my TechHelp page, where you can reach out or search for common solutions. My website also has active message forums and links to my social media pages, so you can connect with other users and get community assistance anytime. For those who are new to Microsoft Access, remember I offer a free three-hour Level 1 tutorial, with Level 2 available for just one dollar if you decide to continue. All relevant links and resources can be found on my website. 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 ListUsing the Replace function in Access queriesStripping non-numeric characters from phone numbers Creating a select query to identify unwanted characters Step-by-step use of Replace for multiple characters Chaining Replace functions to clean multiple character types Removing spaces from phone number fields Saving queries for repeated use Designing an update query to overwrite original data Referencing calculated fields in update queries Nesting queries for data cleanup Best practices for backing up data before updates |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access Replace Update replace function PermaLink Replace Function in Microsoft Access |
