Replace accented characters for non-accented characters-CodePudding

On MS Access, I'm "cleaning up" one of my db tables. And one of the things I must do is replace accented chars with non-accented chars. But I really can't come up with anything else than a replace statement. But when I try to run it, I get the "Expression too complex" error.

This is what i'm inputing on the query: select REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Names],'é','e'),'ê','e'),'ë','e'),'è','e'),'É','E'),'È','E'),'Ê','E'),'Ë','E'),'ð','D'),'Ð','D'),'â','a'),'à','a'),'á','a'),'ã','a'),'æ','a'),'à','a'),'å','a'),'Å','A'),'À','A'),'Á','A'),'Â','A'),'Ã','A'),'Æ','A'),'ä','a'),'Ä','A'),'ï','i'),'î','i'),'ì','i'),'í','i'),'Ì','I'),'Í','I'),'Î','I'),'Ï','I'),'ô','o'),'ò','o'),'ó','o'),'õ','o'),'ø','o'),'Ò','O'),'Ó','O'),'Ô','O'),'Õ','O'),'Ø','O'),'ö','o'),'Ö','O'),'û','u'),'ù','u'),'ú','u'),'Ù','U'),'Ú','U'),'Û','U'),'Ü','U'),'ü','u'),'ñ','n'),'Ñ','N'),'Ç','C'),'ç','c'),'ý','y'),'ÿ','y'),'Ý','Y'),'þ','T'),'Þ','t'),'ß','ss') from [tblnames]

Any help on this matter?

CodePudding user response：

To permanently change data, can manually run 60 operations with Ribbon Find/Replace wizard.

Or build a table that maps these replacement pairs and run VBA procedure like:

Sub FixCharacters()
Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblMap")
Do While Not rs.EOF
    db.Execute "UPDATE [tablename] SET [fieldname] = Replace([fieldname], '" & rs!bad & "','" & rs!good & "')"
    rs.MoveNext
Loop
End Sub