Solved

MS Access code to remove alphabet accents

Posted on 2013-10-31
5
858 Views
Last Modified: 2013-12-01
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
Comment
Question by:JCJG
  • 3
5 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39615390
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
 

Author Comment

by:JCJG
ID: 39615403
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39615422
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39615623
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
 
LVL 57
ID: 39616398
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Outlook Free & Paid Tools
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now