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?

Members

There is no extended cut, but here is the file download:

•  File Download - Gold Members

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!

Prerequisites

• Intro to VBA
• While Wend Loop
• Replace Function
• Recordsets

Links

• Field Value Substitutions

Recommended Courses

• Access Developer Courses

Learn More FREE Access Beginner Level 1 FREE Access Quick Start in 30 Minutes Access Level 2 for just $1 Free Templates TechHelp Free Templates Blank Template Contact Management Order Entry & Invoicing More Access Templates

Resources Diamond Sponsors - Information on our Sponsors Mailing List - Get emails when new videos released Consulting - Need help with your database Tip Jar - Your tips are graciously accepted Questions? Please feel free to post your questions or comments below or post them in the Forums.

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