Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access code to remove alphabet accents

Posted on 2013-10-31
5
Medium Priority
?
1,128 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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 58
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

670 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