Change Special characters (á, é, í, ó, ú, ñ, Á, É, Í, Ó, Ú, Ñ)

[Lucyan]

I have an Access 2007 db for both American and International students and I
need to change special characters (á, é, í, ó, ú, ñ, Á, É, Í, Ó, Ú, Ñ) for
those without the accents and tilde a, e, i, o, u, n, A, E, I, O, U, N). I
was having problems trying to do it with Find and Replace because it does not
change all records and going one by one is a hassle.

[KARL DEWEY]

Create a table with two fields - English and European. Populate it with the
characters.

BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE
Then use this in the update query where you have both tables --
Replace([YourTable].[YourField], [English], [European])

--
Build a little, test a little.

[KARL DEWEY]

You will need to run the update query multiple times.

--
Build a little, test a little.

[Clifford Bass]

Hi Lucyan,

I have done some stuff with accented vs. non-accented characters. You
may wish to try the following. It only works with the ASCII values 0 through
255 so if you are dealing with UNICODE values, it will not work.

Private m_varUnaccented As Variant

Public Function RemoveAccents(ByVal strIn As String) As String

Dim intASCIIValue As Integer
Dim intIndex As Integer
Dim intLength As Integer
Dim strOut As String

On Error GoTo Handle_Error

strOut = vbNullString

If IsEmpty(m_varUnaccented) Then
m_varUnaccented = VBA.Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
12, 13, 14, 15, 16, _
17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32,
33, 34, 35, 36, _
37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52,
53, 54, 55, 56, _
57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72,
73, 74, 75, 76, _
77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92,
93, 94, 95, 96, _
97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109,
110, 111, 112, 113, _
114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126,
127, 128, 129, _
130, 131, 132, 133, 134, 135, 136, 137, 83, 139, 140, 141, 90,
143, 144, 145, _
146, 147, 148, 149, 150, 151, 152, 153, 115, 155, 156, 157, 122,
89, 160, 161, _
162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174,
175, 176, 177, _
178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190,
191, 65, 65, 65, _
65, 65, 65, 198, 67, 69, 69, 69, 69, 73, 73, 73, 73, 68, 78, 79,
79, 79, 79, 79, _
215, 79, 85, 85, 85, 85, 89, 222, 223, 97, 97, 97, 97, 97, 97,
230, 99, 101, 101, _
101, 101, 105, 105, 105, 105, 100, 110, 111, 111, 111, 111, 111,
247, 111, 117, _
117, 117, 117, 121, 254, 121)
End If

intLength = Len(strIn)
For intIndex = 1 To intLength
intASCIIValue = Asc(Mid$(strIn, intIndex, 1))
strOut = strOut & Chr$(m_varUnaccented(intASCIIValue))
Next intIndex
RemoveAccents = strOut

Exit_Function:
Exit Function

Handle_Error:
RemoveAccents = "Error #" & Err.Number & ": " & Err.Description
Resume Exit_Function

End Function

You can use it in code:

MsgBox RemoveAccents("á, é, í, ó, ú, ñ, Á, É, Í, Ó, Ú, Ñ")

Or in a query:

UnaccentedName: RemoveAccents([LAST_NAME] & ", " & [FIRST_NAME])

Clifford Bass

[Lucyan]

Thank you very much for all your fast responses. I will try to see if your
solutions work and will update here. Again thank you for your time and help.

Sincerely,

Lucyan

[Clifford Bass]

Hi Lucyan,

You are welcome. And good luck with that!

Clifford Bass