Character Word Substitutions
By Richard Rost
11 months ago
Clean Bad Characters in Access with Substitution Table In this Microsoft Access tutorial, I will show you how to use a character substitution table and a bit of VBA code to clean up and replace unwanted characters or words in your text fields, such as curly quotes, accented letters, and other problematic symbols. You will learn how to create the table, write a simple loop in VBA using a recordset and the Replace function, and perform substitutions to standardize your data. Elliott from Athens, Georgia (a Platinum Member) asks: I often import data into Access that has weird characters in the text fields, like curly quotes, accented letters, or foreign symbols. Is there a way to clean all of that up and replace them with plain text automatically? MembersThere is no extended cut, but here is the file download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, character substitution, word substitution, clean bad characters, high-ASCII character cleanup, curly quote replacement, accented letter substitution, substitution table, VBA Replace function, loop through records, recordset substitution, text field cleanup, misspelling correction, long text field replace
Intro In this video, I will show you how to automatically clean up bad characters and unwanted words from your text fields in Microsoft Access using a custom substitution table and a few lines of VBA code. We'll cover designing and populating the substitution table, mapping bad characters to good ones, using VBA with a recordset and the Replace function to process your data, and updating your forms with the cleaned text. This is a developer-level TechHelp tutorial for anyone needing to tidy up imported data or user input in their Access databases.Transcript Today we're going to talk about character or word level substitutions. And we're going to clean up bad characters. What's a bad character? Any character you don't want. You want to replace it with something else or even whole words inside of a text field, using some substitution and some loops and some other cool stuff in your Microsoft Access database.
Today's question comes from Elliott in Athens, Georgia. Elliott says, I often import data into Access that has weird characters in the text fields like curly quotes, accented letters, foreign symbols, and lots of stuff like that. Is there a way to clean all that up and replace them with plain text automatically?
Yeah, I have this problem myself too, especially on my website, whether it's getting orders from people or people typing things into the forums. I like low-ASCII characters. I don't like curly quotes or m-dashes or any of those weird high-ASCII characters. I like to - it's just easier for the database and for the website to get rid of those characters.
So in this video, I'm going to show you how to do that. We're going to make a substitution table.
Before we do that, though, this is a developer level lesson. Could you do this without code? Yeah, you probably could. But this is one of those things where it's so much easier to do with just a couple of lines of code than it is to try to come up with update queries and this and that. You have to loop through the characters or loop through the substitution characters either way. It's going to involve a little bit of code. It's possible to do it without code, but I wouldn't want to. So this is a developer level lesson.
What does that mean? Well, we're going to use some VBA - Visual Basic for Applications. If you've never used any VBA before, go watch this video. Here's the link. I'll put it down below. It's about 20 minutes long and it'll teach you everything you need to know to get started.
We're going to also use a while loop, so go watch this video. We're going to use the Replace function to replace something inside of a text with something else. And we're going to use a recordset to make the magic happen in the first place.
Go watch all of these videos. They're free. They're on my YouTube channel. They're on my website. Then come on back.
Here I am in my TechHelp free template. This is a free database you can grab at my website if you want to.
Now, you could hard code all these substitutions in your VBA. If it's a curly quote, then replace it with this. We're not going to do that. We're a little more advanced than that. We're going to make a table with all the substitution characters in it.
So, create table design. We're going to call this our character substitution table. We'll have a character subst ID. I'm not above abbreviating long stuff, and that will be our auto number. We'll have a bad character and a good character. Are you a good witch or a bad witch?
All right. Save it. CareSubstT. Now we have to fill it in with some stuff.
Now, I have a whole list of stuff already because I've been doing this in my database for years. Let me just grab some of the stuff. In fact, I'm going to just copy and paste my data from my table.
There we go. So, there's a curly quote. That's an open curly quote. I think I have the close curly quote now. Here, both of those I want to convert over to regular double quotes. Same thing with the single quotes. I hate these things. M-dashes - we're going to replace both of those with a hyphen. An ellipsis is going to be three periods.
Then you have all your stuff with the accents over, all your e's, your a's, your o's. There's more, there's i's.
You can also use this for substituting complete words. So if you see someone type in that, it'll convert it to this, or if they type in this a lot, make it FBI. You can use it for misspellings. Misspellings can be miss - see, it auto-corrected me. I want misspellings tab.
I'm going to come back here. M-I-S-P-E-L-L-I-N-G-S. Tab. It did it again. I'm going to stop doing that. I don't want to stop doing that. I'm going to just change this one back. All right. And that's auto-corrected. Now, it'll run in Access if your users are typing that in directly. But again, if you're dealing with imported data, who knows what you're going to get. Misspellings. There we go.
You can use this for all kinds of substitutions inside of a text field.
That brings to mind - hang on - I was also going to mention that a couple of weeks ago, I did this one on field value substitutions. It's the same thing, but it's the entire field. We did this with countries, so if they type in their country name wrong, this replaces the whole field.
This was a three-part series and it's expert level. So we did it without any programming. Go check this out too, optionally, if you want to. I'll put a link down below.
All right. Now I have my table all set up. There are the bad characters. There are the good characters. Now, what are we going to do?
Let's just pick a field. We'll work with our notes field in here. You can do this with any field if you want to. It'll work anywhere, really. Let's design this guy.
Just for the purposes of class, I'm going to get rid of this stuff, and I'm going to get rid of that. We're just going to make this button here our FixIt button. Let's make this a little bigger so we can see all the text that's in there.
All right. What are we going to put in our FixIt button?
Build event. Here's my code builder, and we're in the contact button, and we're going to put our stuff in here. That's fine. We can get rid of this because we just deleted that. This is now our FixIt button. You should go rename it so Alex doesn't yell at you, but I'm not going to.
We need a couple of things. We need a recordset so we can loop through all of the items in our FixIt table. We want to loop through each one of these records and change using the Replace function. If it finds that, replace it with that.
Now, I have 25 items in here. I'm guessing, especially if you're using long text fields, that there will be fewer records in here than there will be characters in your long text. So it's probably going to be faster to loop through this than it would be to loop through each of the characters.
We're going to loop through the records in that table and then just replace the text in here every time it finds one of those characters. Pretty straightforward.
So we're going to Dim rs as a recordset and s as a string. Why s as a string? Well, I'm going to throw the notes in there to begin with. s = notes. Notes is the field on the form.
Whenever I'm doing something like this with a bunch of loops, I prefer to work with a memory variable over a form field. I don't like to have to constantly change that form field, so I'm going to put it in a memory variable and then when I'm all done, I'm going to put it back in the notes field.
I just have found in my experience that it works best that way. Take the text box, load it into a local variable, and then when you're done, put it back.
Again, you don't have to. This is just my thing. It's one of my things I do.
Set rs = CurrentDb.OpenRecordset. What are we looping through? The CareSubst table.
While Not rs.EOF
Do some stuff
rs.MoveNext
Wend
And then when we're done, we're going to rs.Close and set rs = Nothing.
Okay. We have our shell of our recordset done. I always like to write the whole shell and then I'll put the filling inside the donut later.
So what's the filling in here? What are we going to do? Well, we're going to look and see if we find the bad character, and we're going to replace it with the good character. We can do that in one shot. s, the whole string, equals Replace(s, rs!BadCharacter, rs!GoodCharacter), just like that. And then we'll put that right there.
That's it. The Replace function is going to replace every instance of the bad character with the good character everywhere it finds it inside of s, and it's going to put it back into s. So, as we loop through this, it's going to replace all the bad characters with all the good characters. And that's it. We're done.
Thank you for the extra spaces. Ctrl+Y. Use Ctrl+Y, by the way, to get rid of empty spaces. Be careful though. Ctrl+Y actually will take it and put it into the same as the cut command. It removes the line and puts it inside of your clipboard.
Okay. Save it. Debug compile once in a while. We're going to close it. Open it.
Now, we can put some characters in here, but I also copied something. I put it in my notes over here. Hang on. Let me copy and paste this. This is pretty cool. I have a paragraph that has a whole bunch of those characters in it.
There we go. Jose said I'll be there at five. Don't see all the little single quotes and the little who's there and this and that? I'm going to sneak the word NASA in here too.
Ready? Fix it. Boom. Done. It looped through every record in that table. Substituted all the bad characters with the good characters.
Now, you just have to look it over and see if you've missed any. If you have one that isn't in there, just go add it to your table and hit FixIt again.
That's pretty straightforward. Pretty cool. And that was realistically about one, two, three, four, five, six, seven, eight, ten lines of code. See?
I've always said once you learn just a little tiny bit of VBA programming, you can make your Access databases a whole lot more powerful. It's not hard to do, folks. This is not rocket surgery or brain science or whichever one of those.
Do you like learning with me? Do you like my videos? Do you want to learn some more? I have tons of developer lessons on my website. I'm just getting ready now to release Access Developer Level 50. I have tons and tons of developer classes on my website where I take you from the beginning and we walk through it all the way it's supposed to be taught. I don't skip around like we do in the TechHelp videos. You don't have to go learn this thing, go learn that. It's all taught in a nice continuous format.
Come and check it out if you want to learn more about programming and VBA with Microsoft Access and take your databases to a whole other level.
That's going to do it for today, folks. That is your TechHelp video. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Creating a character substitution table in Access
Designing fields for a substitution table
Populating the substitution table with entries
Examples of bad character and good character mappings
Using the substitution table for word-level replacements
Using VBA to automate substitutions in text fields
Loading field data into variables for processing
Using a recordset to loop through substitution rules
Applying the Replace function in VBA for substitutions
Updating form fields with cleaned text
Testing substitutions by running the code in Access
COMMERCIAL:
In today's video, we're going to learn how you can automatically clean up bad characters and unwanted words from your text fields in Microsoft Access using a handy substitution table and just a few lines of VBA code. You'll see how to set up your own bad-to-good character swaps, including curly quotes, accented letters, or even full words, and use the Replace function with a While loop and a recordset to clean up your imported data or user input the easy way. Get your database looking sharp in minutes. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the main reason for performing character or word level substitutions in a Microsoft Access database?
A. To increase the length of text fields for more storage
B. To replace unwanted or problematic characters or words with preferred replacements
C. To make the database compatible only with foreign language symbols
D. To speed up database transactions unrelated to text fields
Q2. Why is it generally easier to perform these substitutions using VBA code rather than update queries in Access?
A. Because VBA is required for all Access database functions
B. Update queries cannot handle text manipulation at character or word level
C. VBA allows for flexible looping and substitution with fewer lines of code
D. Queries can only substitute words, not characters
Q3. What is the primary purpose of creating a substitution table in the context of this video?
A. To hardcode all possible substitutions directly in the code
B. To store all unwanted characters and their desired replacements for dynamic substitution
C. To create a backup of the original data
D. To convert all data into uppercase
Q4. In the substitution table described, what are the key fields?
A. UserName, Password, CharacterSubstID
B. BadCharacter, GoodCharacter, CharacterSubstID
C. TableName, FieldType, SubstituteValue
D. PrimaryKey, DateCreated, ReplacementValue
Q5. What Microsoft Access function is used in the video to perform the actual replacement of characters or words within a string?
A. Substitute
B. Change
C. Replace
D. Swap
Q6. What is the recommended method for updating the field contents during the substitution process in this video?
A. Update the form field directly after each substitution
B. Store the text temporarily in a memory variable, make changes, then write it back to the form field
C. Build a concatenated string from all records and update it at once
D. Update each character one at a time within the form field
Q7. In the sample code provided, how is the substitution table accessed for looping through substitutions?
A. By importing the table into Excel first
B. By opening a recordset on the substitution table and looping through its records
C. By using a SQL join query on the form
D. By copying all records into a temporary array at runtime
Q8. When looping through the substitution table, how does the code decide what to replace?
A. It checks each individual character and replaces it with the table's field names
B. It uses the Replace function to substitute each bad character with its good counterpart as found in the recordset
C. It determines the most common character and replaces it everywhere
D. It counts the frequency of each word and substitutes those that appear the most
Q9. The presented technique can handle which types of substitutions?
A. Only single character substitutions
B. Both single character and full word substitutions
C. Only word substitutions, not characters
D. Only numeric value replacements
Q10. Why is looping through the substitution records generally faster than looping through each character in a large text field?
A. There are usually fewer substitution records than there are characters in long text fields
B. Each character takes longer to process than each database record
C. Text fields cannot be looped character by character in VBA
D. The Replace function requires entire records as input
Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-B; 7-B; 8-B; 9-B; 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.Summary Today's TechHelp tutorial from Access Learning Zone focuses on character and word-level substitutions to help clean up unwanted or problematic characters in your Microsoft Access database. The main goal here is to replace any character or even whole words you do not want—such as curly quotes, accented letters, foreign symbols, or any other unusual text entries—with plain text automatically.
This topic actually comes from a frequent issue that arises when importing data into Access. Often, the imported data contains non-standard characters like those curly or smart quotes, m-dashes, accented letters, and so on. These characters can cause problems, particularly if you prefer sticking with straightforward, low-ASCII text that behaves consistently across your website or database applications.
So, how can you tackle this? The best method, in my experience, is to create a substitution table in Access that defines which characters to replace and what to replace them with. While you could attempt this process without using any code—maybe relying on update queries—the reality is that a little VBA makes it much easier and much more flexible.
This is a developer-level lesson, which means you will need to use some Visual Basic for Applications (VBA). If VBA is new to you, I highly recommend reviewing some basic introductory material first. There are several resources on my site and YouTube channel that cover getting started with VBA, while loops, and using the Replace function.
With a solid understanding of those basics, you can proceed by creating a new table in your Access database to store your substitution rules. In the table design, add an AutoNumber primary key (like CharacterSubstID) and then fields for the BadCharacter and the GoodCharacter. This will serve as a lookup table for all the characters or words you plan to substitute. I suggest filling it out with all the common offenders—curly quotes, m-dashes, ellipses, accented letters, and any other problematic entries you routinely encounter. This can also extend beyond single characters; you can include entire words for things like common misspellings or abbreviations.
Once your table is set up and populated, you can integrate the substitution process into one of your forms. For this lesson, let's use a button (I'll call it FixIt) that, when clicked, processes a field in your form—such as a Notes field—and cleans up the text by replacing all the bad entries defined in your substitution table.
Behind the scenes, the code for this button will set up a recordset to loop through all records in the substitution table. For each record, it checks for occurrences of the BadCharacter in the target string and uses the Replace function to swap in the GoodCharacter. Throughout the looping process, it's best practice to work with a variable in memory, not directly with form controls, for both speed and reliability. Once all substitutions are made, you write the cleaned-up text back into the field.
This approach works efficiently, especially as you are likely to have fewer substitution rules than the number of characters in long-text fields. It provides a flexible, easy-to-maintain solution—if you ever encounter a new undesirable character, simply add it to your substitution table and rerun the process by pressing your button again.
For even more flexibility, remember that you can use this method not only for single characters but also for replacing entire words or phrases, which is particularly handy for fixing misspellings or standardizing abbreviations in bulk.
If you're interested in related topics—like making substitutions for entire field values, such as correcting country names—you can find additional lessons on my website. Those cover expert-level approaches to handle field value substitutions using both queries and code.
In summary, the technique covered here shows that with a little bit of VBA in Access, you can greatly improve the quality and consistency of your imported and user-entered data. It's a manageable, highly effective way to clean up unwanted text in your databases.
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 Creating a character substitution table in Access
Designing fields for a substitution table
Populating the substitution table with entries
Examples of bad character and good character mappings
Using the substitution table for word-level replacements
Using VBA to automate substitutions in text fields
Loading field data into variables for processing
Using a recordset to loop through substitution rules
Applying the Replace function in VBA for substitutions
Updating form fields with cleaned text
Testing substitutions by running the code in Access |
