How to Remove Unwanted Characters from a Microsoft Access Text Field with a Custom Filter Function

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?
Silver Members and up get access to an Extended Cut of this video. 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.
MEMBERS VIDEO:
• Filter Characters MEMB...
BECOME A MEMBER:
RUclips: / @599cd
or My Site: 599cd.com/THMe...
LEARN MORE:
599cd.com/Filt...
LINKS:
Intro VBA: 599cd.com/VBA
AfterUpdate: 599cd.com/Afte...
For Loops: 599cd.com/ForNext
Create Function: 599cd.com/Crea...
Input Masks: 599cd.com/Inpu...
String Functions: 599cd.com/Stri...
SUGGESTED COURSE:
Access Developer 3: 599cd.com/ACD3
GOLD MEMBER CODE VAULT LINKS:
Filter Characters: 599cd.com/Filt...
Filter w Replace: 599cd.com/Filt...
OTHER COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Cont...
Order Entry & Invoicing: 599cd.com/Invo...
More Access Templates: 599cd.com/Acce...
ADDITIONAL RESOURCES:
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/Tech...
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.o...
products.offic...
microsoft.com/...
KEYWORDS:
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
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[@BanditFX7]

Thank you richard, you are a goldmine

[@599CD]

Wow, thanks

[@hassanrozh6579]

Thanks so much, for sharing this video, really by your great ideas, you move the MS Access to another level, of course that is come from your love to MS Access. we appreciate it.

[@599CD]

You are so welcome!

[@AzulonKaiba]

Interesting to see your way to replace the unwanted characters. Another way to change the phone would be using regular expressions object with VBA.

[@599CD]

Sure

[@woodturner1954]

Just found you you tune channel while searching for MS access and Genealogy. Watched the 2 into videos but could not determine what the other 7-8 are named. Do you have a play list for your Access Genealogy series as mentioned?

[@599CD]

Here ya go: 599cd.com/GenDB

[@costastsantires8098]

Many thanks, suppose we need to exclude some characters such as "tab", sometimes we copy paste and the source has a tab, then it is very tricky to work with the field when a "tab" is sitting there, well, when we want to exclude some characters, we make the code like this ? If Cpos = 0 Then (insteaf of If Cpos 0 Then...)

[@599CD]

The tab character would be filtered out if it's not in the allowed list.

[@rabidfollower]

When you call the function here (8:35), you need to write FilterCharacters(phone & "", ...), i.e. concatenate phone with a double double-quote "" to ensure it is always a string, because the function expects a string there. If the user deletes a phone number on the form, the phone field will become null, and FilterCharacters(phone, ...) will cause a run-time error. But FilterCharacters(phone & "", ...) will still work, and in fact return the correct result.
Your students should still benefit from your video since it is mainly about filtering. They shouldn't copy your code verbatim anyway, even when it's error-free. You are teaching them lessons, not doing their homework for them. They should learn from your lessons and write their own code to suit their individual needs, unless they hire you as a consultant who codes for them.

[@599CD]

Good points all around. You could also wrap it in the NZ function:
Phone = FilterCharacters(Nz(Phone, ""), "1234567890")
I don't always think of ALL of the possibilities when I'm prepping a video/class. These things usually come up later in the usability phase, or as I like to call it, the "oh shit, bug!" phase. :)