Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access code to remove alphabet accents

Posted on 2013-10-31
5
Medium Priority
?
1,166 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 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

885 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