Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1287
  • Last Modified:

MS Access code to remove alphabet accents

Hi, I'm comparing Spanish names in two different tables but the some are written with the accent marks and some are not, such as Álvarez vs Alvarez.  Is there a way to eliminate the accents so the names will be the same?  Thanks.
0
JCJG
Asked:
JCJG
  • 3
1 Solution
 
mbizupCommented:
You can use the replace function like this:

UPDATE YourTable
SET YourField = Replace(YourField, "Á","A")

Open in new window


You can use similar commands or even a nested replace command to remove different accents from your field.
0
 
JCJGAuthor Commented:
Is there a way I can do all alphabets?  I know there are those like ñ,é, and í etc.  Do I have to do them one by one?
0
 
mbizupCommented:
Sort of... you do have to account for each individual accented character, and it's replacement.

However, there are plenty of articles and examples of custom functions to do that.

Here is one:
http://www.jpsoftwaretech.com/remove-and-replace-special-characters-in-vba/

I haven't tested it myself, but it looks like it should do the trick.  You just need to look at the list of accented characters and replacements in that function, and if your data has any that are not accounted for there you would have to add them yourself (this is straight forward and well explained in the article).
0
 
mbizupCommented:
Just one thing that might require more explanation, if you want to use that function in a query, you would add it to a standard module and write your UPDATE query like this:

UPDATE YourTable
SET YourField =  ConvertAccent(YourField)

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I went down this road not too long ago and one of the things your going to find out quickly is that it is not as simple as it sounds.

The characters represented depend on the code page that's been used with the database, so basically you need a translation table for every possible code page.

Also, depending on the  application that generated the character, it may be a uni-code character (two bytes wide) embedded in the string.

In my case, I was trying to scrub name and address info entered on web orders before importing to a back end system.   The backend system used EDI to send orders to a warehouse for shipment, and EDI doesn't like anything outside of the normal ASCII characters.

 Long story short; I gave up on it.   There was no way that I could determine if a unicode character was cut and pasted into the field, and if I translated it based on my character mapping, it actually changed the meaning of it ( 'A' became 'B' for example).

 I felt like I was missing something in identifying a uni-code character vs noraml single byte ones since it displayed correctly, but I never could figure out how it was being identified.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now