Filter Characters
By Richard Rost
4 years ago
Filter Characters from Text Field in Microsoft Access
In this video, I'm going to teach you how to write a function to filter out unwanted characters from a text field (or string) in Microsoft Access. We'll call it the FilterCharacters function.
Tom from Buffalo, Wyoming (a Platinum Member) asks: I know that you can force users to enter only digits into a phone number field by setting up an Input Mask. However, we do a lot of copy and paste from emails and such. That doesn't work with an Input Mask. Is there any way we could allow the user to paste a phone number into the field and then just remove anything but the digits?
Members
Members will learn how to make global constants such as ALPHA and NUMERIC to represent long strings of characters, so that we don't have to copy those all over the place in our code. Also, we'll add an optional third parameter which will be the string that unwanted characters will be replaced with, like a space character.
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!
 Suggested Course
Links
Gold Member Code Vault
Keywords
access 2021, access 2019, access 2016, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, filter, allowed, remove, Removing characters from fields, Remove Characters from Text Field, replace, Remove Characters from String
Intro In this video, I will show you how to filter out unwanted characters from a text field in Microsoft Access by writing your own custom VBA function. We'll look at how to clean up fields like phone numbers after copying and pasting, ensuring only the characters you want are kept. I'll walk through setting up the filter function, applying it to fields using the after update event, and demonstrate how you can modify it for names or other text fields. You'll learn practical VBA basics such as string handling, loops, and integrating custom functions into your Access forms.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to remove unwanted characters from a text field in Microsoft Access with a custom filter function that we are going to write ourselves. Today's question comes from Tom in Buffalo, Wyoming, the other Buffalo. I'm originally from Buffalo, New York, so that's the other Buffalo.
Tom says, I know that you can force users to enter only digits into a phone number field by setting up an input mask. However, we do a lot of copy and paste from emails and such. That does not work with an input mask. Is there any way we can allow the user to paste a phone number into the field and then just remove anything but the digits?
Yes, Tom, of course we can do that. In fact, let's write our own custom filter function so that we can filter out anything but the characters that we want, the digits for example.
Now this will be a developer video. So if you have not watched my intro to VBA class, go watch that now. It is a free video, it is about 20 minutes long. Do not let VBA scare you. We only need to write about maybe six or seven lines of code to get this to work.
After that, watch my after update video because we have to put this code in the after update event for whatever field we want to check. You will need to know how to do for next loops, so go watch that video. We will be creating our own custom function, so I have a video for that. And if you do not know what input masks are, you might want to go watch that one too. Watch all these videos if you do not know what the stuff is and then come on back.
So, what Tom is saying, if we put an input mask in here, on the phone number field for example, let's go with 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0. That means you have to put in all 10 characters.
If you come in here, you go 239, 555, whatever, whatever, we are good. But if we have an email we are copying from, let's say we copy the phone number here, we will come back over here and try to paste it. Now it will not paste it because those characters, the spaces, even the dash, the parentheses are invalid for the input masks.
So what we are going to do is we are going to get rid of the input mask, and then we are going to allow the user to just paste them whenever they want and we will have our after update event fix it.
Once this happens, paste, hit tab, and then our function will fix that.
So how do we go about doing that? Well, let's make ourselves a function.
We need a global module first. So we are going to go create and then module. There is my VB editor. Let me resize it just a hair.
We are going to make this a public function. That way everybody can use it: filter characters, I am going to call this. It is going to take two parameters: in string as a string and then characters to allow as a string as well. I spelt "allow" wrong, of course. And that is going to return a string value.
So it is going to take two strings in, one, two, and then it is going to return a string value.
The first thing we are going to send it is what is the string that the user typed in, so the full number with the funky characters in it. And then the characters to allow is going to be a string of characters that are allowed to be in the string. Everything else will get filtered off.
We are going to need some variables in here. So dim, let's call it out string as a string; x as a long (we are going to have to loop); c as a string (that is the single character as we loop through the allowed characters, I will explain all this in just a minute); and then we are going to call another one c position as a long.
First, set out string to nothing. Set it equal to an empty string. Now we are going to loop through all the characters in the in string. So for x equals one to the length of in string, and then next, that is our loop.
We are going to loop through this guy. So if that is 555-1212, that is seven characters, for example.
Now for each one of those characters, we are going to check and see if it is in the characters to allow string. Let me do that like this. c, which is our one character string, c equals mid(in string, x, 1). The mid function says go into this string at this position and give me one character.
For example, I will put a string up here, 716-555-1212. That is where I was from, Buffalo, New York, see, Buffalo. So x is going to start at one, so give me the first character: c is the mid of in string, one, one, that guy. And then, when it loops again, x will be two, so it will say give me that, the second character, give me one of them. That is how mid works.
I have got another whole video on left, right, mid string. I will put a link to that down below in the link section too.
So now that I have got that character, I got this character right here, for example. I am going to check now to see if it is in the character to allow string. So, c position equals in string(char to allow, c). The in string function says is this string inside that string? So if it is the letter M, this will return a zero, it does not exist. If it is the number six, it will return a three, its position three. It always returns as the first one.
So we have got a character at a specific position. Then we are checking to see if that character is inside the characters to allow string. So it returns a zero if it does not find it. So we are going to say if c position is not zero. Greater than and less than means not equal to.
If it is not zero, then we are going to add that to our out string. Out string equals out string, concatenate c. So take that c, put it on the end of our out string. Then our next, and then we are all done: filter characters equals out string. We make the name of our function equal to this variable.
What is going to happen? It is going to loop through one to however many characters that is, check to see if that is in the allowed string. If it is, add it to the out string, and then loop next. Is there a one? Is that in this allowed character string? No. Okay, yes it is. When we get to this hyphen, that is not allowed, so it just will not get added to the out string.
Now we just have to put this in the after update event for whatever field you want to check.
I will save this. This will be my global module. I usually have a global module where I put all the small functions that I have got like this. You do not want a separate module for each one of these. You can have tons of modules in your database.
Let's go in here, right click, design view, phone number, event, after update.
By the way, I already noticed in your copies of Access, one of my moderators, Scott, mentioned this to me this morning. He is like, did anybody notice that Access kind of changed colors over the last week? They must have pushed an update at Microsoft. This used to be white, now it is gray. I kind of like it. This changed a little bit too, this guy. A little bit different, little tiny teeny upgrades, just to make me have to update all my videos. Just kidding.
After update. After the phone number gets updated, what are we going to say? Phone equals filter characters. What are we sending to it? Phone, that is a string, comma, what are the characters to allow? One, two, three, four, five, six, seven, eight, nine, zero. Those are all the characters we are going to allow.
Save it. Now come back out here. Let's close this.
Customer form. I am just going to make a change to this one, just hit space bar, that is enough to trigger the after update. Hit tab. There you go.
You can go to your email, if you have a phone number that is in this format, or whether it is like this, no matter what you have, anything, you just take this, copy it, come back over here, paste it in, tab, and the filter characters does the work. Copy, paste, and it gets rid of all those characters.
You can do the same thing with your name fields if you want to. Let's say, right click, design view, email addresses, for example. You might want to have a list of characters that are not allowed in email addresses. I have a list of it somewhere, I had to look them up for my website when people tried to type in email addresses.
But let's just do a name real quick. Go to after update event. First name equals filter characters(first name, ...). If you put it inside quotes, you will actually send the word "first name" to it. Comma. What characters are allowed in a name? A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z. You might also want to allow single quote, a dash, a space. Do not forget your space for names. Any other characters you want to allow in the first name.
Since we have option compare database on, it does not matter if you have upper or lower case here, both will be allowed.
So if I come in here and save it and type in Joan like that, tab, it is good. But if I type in something like "J0an," those numbers get filtered right out, which of course you want to be careful with names because if you have customers from all over the world, and some other languages have characters that are not on the standard 26 key keyboard, for example, e with an umlaut over it or whatever it is.
If you type in something like that, it just gets fixed to that.
Of course, filter characters is also found in my code vault. This is a benefit perk for Gold members and up. I have tons of different functions in my code vault. Lots of stuff in here. One of the perks of becoming a member is signing up, becoming a Gold member. There is all kinds of stuff in here, generic VBA, Access-specific VBA, ASP, even a little bit of Excel VBA, not much though. I have not gotten into much Excel VBA.
If you like programming and you want to learn more about how to add VBA to your Access database, check out my developer lessons. I have 37 of them now and counting. Lots of stuff to learn. Specifically, Access Developer 3 covered a lot of what we did today. For example, our for next loops. We have variables and dimming variables, and stuff like that. All kinds of cool stuff. Access Developer 3. I will put a link to it down below.
If you have any questions, feel free to post them. If you want to learn more, in the extended cut for the members, we will go over global constants. In other words, instead of having these long strings of letters and numbers and whatever other characters inside all of your code, we will just make one thing called Alphanum, which is like alphanumerics or just digits, which is zero through nine. You just put the word "digits" in there.
We will do an optional replace with parameter. As you can see right here is a space. For example, if you get an address and there are a bunch of weird characters in there, it will just replace them with a space. I had this problem with some credit card software that I wrote. It was trying to pass it to the credit card processor, but because there was a weird character in the string, it kept throwing up an error. So once I filtered out anything but letters and numbers, it worked. That is all covered in the extended cut for members.
Silver members and up get access to all of the extended cut videos, all of them. There are like 250 some now. Gold members can download these databases and they get access to the code vault, which is where all the cool stuff, like my filter characters function, can be found.
How do you become a member? Click on the join button below the video. After you click the join button, you will see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page. You will be shown in each video as long as you are a sponsor. You will get a shout out in the video and a link to your website or product in the text below the video and on my website.
But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more and they will always be free.Quiz Q1. What is the main goal of the custom filter function demonstrated in the video?
A. To allow only numeric digits in a text field by removing all other characters
B. To enforce unique phone numbers in a database
C. To change the font of the input field in Microsoft Access
D. To encrypt the text field for security purposes
Q2. Why does the standard input mask not solve Tom's problem when copying and pasting phone numbers?
A. Input masks do not support numeric input
B. Input masks do not allow pasting at all
C. Input masks accept unwanted characters like dashes and spaces
D. Input masks do not filter out non-digit characters when pasting
Q3. Where should the filter function be called to ensure that pasted or typed data gets cleaned up in the form?
A. Before Update event of the table
B. After Update event of the field
C. On Click event of the form
D. On Load event of the database
Q4. What are the two parameters required by the filter characters function?
A. The field to filter and the table name
B. The original string and the allowed characters string
C. The mask string and the replacement string
D. The database name and the user ID
Q5. If you want to filter characters for a first name field, what should you include in the allowed characters string?
A. Only lowercase letters
B. Only uppercase letters
C. Letters (A-Z), possibly apostrophes, dashes, and spaces
D. Only numbers
Q6. What does the Mid function do in the context of the filter characters function?
A. It finds the position of a substring within a string
B. It replaces unwanted characters with spaces
C. It extracts a single character from a specific position in the input string
D. It compares two strings for equality
Q7. What will happen if a character in the input string is not found in the allowed characters string?
A. It is added to the output string
B. It is removed from the output string
C. It triggers an error message
D. It gets converted to its ASCII value
Q8. Why should you consider special characters for names when filtering input?
A. Because names might contain numbers only
B. Because names from different languages may have characters not on the standard English keyboard
C. Because spaces are not allowed in names
D. Because Access does not support international characters
Q9. What is a 'global module' and why is it used here?
A. A module with global error handling for the database
B. A standard module that makes functions available throughout the database
C. A module used for storing database settings
D. A module that controls user access levels
Q10. What is one benefit of becoming a Gold member according to the video?
A. You can access only the free beginner classes
B. You get downloads for all sample databases and access to the code vault
C. You can post unlimited questions on the forum
D. You get Microsoft Office for free
Answers: 1-A; 2-D; 3-B; 4-B; 5-C; 6-C; 7-B; 8-B; 9-B; 10-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.Summary Today's video from Access Learning Zone covers how to remove unwanted characters from a text field in Microsoft Access by creating a custom filter function using VBA. This was inspired by a question about handling phone numbers from Tom in Buffalo, Wyoming.
Tom pointed out that while input masks in Access can force users to type only digits into a phone number field, they're not much help when users copy and paste information from emails. Input masks will reject pasted phone numbers containing spaces, dashes, or parentheses, making the process frustrating. Tom wanted to know if there's a way to let users paste a phone number and then automatically filter out everything except for the digits.
The answer is yes, and the solution involves writing your own custom VBA function. If you have never used VBA, I recommend watching my free introductory video to get familiar with the basics. It's also helpful to understand the after update event, for next loops, creating custom functions, and input masks, so you may want to review those concepts if they're new to you.
To address Tom's problem, the approach is to remove the input mask on the phone number field so users can paste whatever they want, and then use VBA to clean up the pasted data after the fact. This is done by tying the filter function to the after update event of the field. When a user pastes a phone number and tabs out, the function runs and strips out all unwanted characters.
To create the filter function, you start by writing a public function in a global module. The function accepts two parameters: the input string (what the user typed or pasted) and a string that contains the characters you want to allow (like the digits 0 through 9 for phone numbers). The function loops through every character in the input. For each character, it checks if that character exists in your approved list. If it does, it's included in the output string. If not, it's ignored.
After building this function, add a call to it in the after update event of the field in your form. When the field updates, the code will take the current value of the field, filter out unwanted characters by only allowing digits, and update the field accordingly.
This method can be used with any text field. For example, you can apply it to name fields by specifying which letters and characters are allowed in names, such as the standard alphabet, spaces, hyphens, and single quotes. It's important to be mindful of international names and characters that might not be in your restricted list.
For more advanced customization, the function can be adjusted further. In the extended cut for members, I show how to use global constants to make reusable sets of allowed characters, like digits or alphanumerics, so you do not have to repeat long character lists throughout your code. I also demonstrate how you can replace filtered out characters with spaces instead of just removing them. This is useful in real-world scenarios, like when handling addresses or passing data to other systems that reject unexpected symbols.
If you're interested in more functions like this one, my code vault is available to Gold members, containing dozens of useful routines for VBA in Access. Also, my developer classes go deeper into VBA programming techniques relevant for customized database development. Access Developer 3, for example, covers basic programming concepts like variable usage and for next loops, which form the foundation for today's solution.
If you want to access extended cut lessons, download sample databases, or explore more advanced features, consider joining at the Silver, Gold, or Platinum level. Each level offers additional benefits—like monthly classes, database downloads, higher priority for answering questions, and access to my full range of courses for Access and other subjects like Word and Excel.
Remember, my free TechHelp videos will always be available for everyone. For those looking to elevate their Access skills, membership provides in-depth resources and direct support.
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 custom VBA function to filter characters in Access
Declaring variables for string manipulation in VBA
Using the Mid function to extract characters from a string
Using the InStr function to check if a character is allowed
Building an output string by filtering unwanted characters
Assigning the filtered result to a form field in the After Update event
Applying the filter function to phone number fields to keep digits only
Applying the filter function to name fields to allow specific characters
Using option compare database for case-insensitive character comparison
Implementing the function in a global VBA module in Access |
