Could try VBScript Regex

Or a combination of Trim, and multiple Replaces

Filter Characters

Removing spaces in a string is straightforward with the VBA code:  s = Replace(s, " ", "")

To remove special characters in a string, put all the special characters in a string, retrieve each one with the Mid() function, and use Replace() to remove it from your string.  See picture below for a short VBA procedure to do this.

But back to your original goal.  Finding duplicate names and addresses could be a tall task.  Using partial address info the way you do it may yield incorrect results, both false positives and false negatives.

False positive - the addresses below would yield the same unique key of NEW102BRO100 with your method.  In a multi-floor business building, for instance, all companies share the same street address and several company names often share the same first three letters:
     New York Fashion, 102 Broadway, Floor 10, New York, NY 10018
     Newton Buying Office, 102 Broadway, Floor 8, New York, NY 10018

False negative - the addresses below are one and the same but entered differently, and your method wouldn't catch them:
     Basement Stores, dba. Clothes Quarters, 8615 Olive Blvd., St. Louis, MO 63132
     Clothes Quarters, 8615 Olive Blvd., St. Louis, MO 63132

In my old job I tried to tackle this task and it was not a trivial one, and we had thousands of customers.  I had to take into account whether abbreviations are used or not, zip code is 5 or 9 digits, company name is complete or shortened, alternate city name is used or not, etc.  For instance, the two addresses below would be considered the same, even though the company name, address, and city are all different:

Birdies
4978 N. University Dr.
Lauderhill, FL 33351

Birdies Fashions Inc.
4978 North University Drive
Fort Lauderdale, FL 33351-2468

An ideal solution would be to "auto-correct" an address upon entry, the way many shopping sites do it when you enter your shipping address.  That way, all addresses would be, in a way, standardized, which would make it easier to check for duplicates.  For instance, "FL 8" would be the standardized way to denote the 8th floor, 8th Fl., Floor 8, etc.  In my old job, we never went that far.  We left many duplicates in our records.  When we spotted one, we simply move all related records back to the correct customer, then removed the duplicate.

Sorry for the lengthy post.  It's just my way of expressing that this is not a trivial task by any means.

Thanks, Kevin. That's basically what my FilterCharacters function does. :)

Thank You both.  This is hard to do because I am a truck driver and I can only take my classes and work on my database when I am parked for longer periods than my 10 hour breaks.  I appreciate all the help!  Mr. Rost, I thoroughly enjoy your lessons!  I have learned soooo much already.  I will attempt to use this code you provided and the videos you have recommended as soon as I have a chance to.  It comes down to having enough signal at the place I end up parked for longer periods of time.

In the code you provided here, Kevin.  What are we replacing the special characters with?

The special characters are replaced with an empty string (the double quotes). Effectively removing the special characters.

Awesome.  Thank you.  After that I can remove all spaces with the filter characters that Mr. Rost refferred to?

Kevin, what do you think about using the first 5 characters in the name, address, and zip after removing special characters and spaces?

String Functions

Hi Andrea, using 3 characters or 5 characters or even all characters won't help you that much because, as I said, addresses can look the same but aren't, or can look different but aren't.  There is also the awful scenario when an address is not a duplicate but Access thinks it is (false positive) and doesn't allow your user to make the entry, which would affect your business and force you to make design changes on your database.  So here is my suggestion...

Have your data entry person do a quick search of a keyword to display any existing addresses containing the keyword, then let the user use his or her *own eyeballs* to spot duplicates.  See the picture below; this is how I did it in my old job.  If I have new address with the word "Alexander", I search "Alexander", and get a list of all existing addresses that have that word.  There could be company names with "Alexander", street names or city names with "Alexander."  (Do not search for zip codes.  In big cities, many companies may have the same zip code.  We had thousands of customers, and 200+ were in the same zip code, which was a busy business district.)

To search if a word exists within a name, you use the InStr() function.  For instance, to search for the word "Alexander" in the first line of the street address, you write the line below, which will return a non-zero value if "Alexander" is found:

     i = InStr(1, [StreetAddrL1], "Alexander")

You run a query that does the same thing for all parts of the address: company name, street addr line 1, street addr line 2, city, state, zip, contact person's name, etc.  The query may look something like:

   SELECT * FROM Customers WHERE InStr(1, [CompanyName], "Alexander") <> 0 OR InStr(1, [StreetAddrL1], "Alexander") <> 0 OR InStr(1, [StreetAddrL2], "Alexander") <> 0 OR InStr(1, [City], "Alexander") <> 0 OR InStr(1, [State], "Alexander") <> 0 OR InStr(1, [Zip], "Alexander") <> 0

This query will return all the results, and it should run pretty fast.  We had thousands of customers and this query returned instantaneous results.

Thank you Again, Kevin.  And Thank you Kevin Robertson for that link.  Iwill watch it.  One more question. The code you provided to remove the special characters, is that supposed to be built as a global function or a private one?

I have built the global function, how do I use the function to remove the special characters and then store the resulting information for later use?

Did you watch the video Richard linked to above?

It can be either a global or private function.  If you need to use it everywhere, make it global, otherwise make it local.  If you need to use it in a query, make it global.  When in doubt, make it local.  You use it like you use any other function:

   return_value = Function(input_values)

In this case, it is:

   s = ReplaceSpecialChars(s)

Kevin Robertson, I am working on that now.  I had to watch several other required videos before I can watch that one.  

Thank you again, Kevin Yip and Kevin Robertson, you both have been such a blessing!