Solved

MS Access code to remove alphabet accents

Posted on 2013-10-31
5
891 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

919 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

20 Experts available now in Live!

Get 1:1 Help Now