0

I'm using MS Access / VBA to interface into a package that gets screwed up by "non-English" text characters so I've had to build code to replace them by acceptable "English" ones. Tedious but not difficult, you may have thought.

Look at the following (simplified) code and results though, and you'll see that there seems to be a problem using the REPLACE function.

? xText  
Hildegard Von Bingen: Geistliche Ges+ñnge

If I use REPLACE against field xText containing "Hildegard Von Bingen: Geistliche Ges+ñnge", it doesn't replace the +ñ with e:

? replace(xText,"+ñ","e")  
Hildegard Von Bingen: Geistliche Ges+ñnge

If I use it against the raw string Hildegard Von Bingen: Geistliche Ges+ñnge" it does the replace as expected:

? replace("Hildegard Von Bingen: Geistliche Ges+ñnge","+ñ","e")  
Hildegard Von Bingen: Geistliche Gesenge

If I try something slightly different against the field, it replaces the H and the h, as expected:

? replace(xText,"H","-")  
-ildegard Von Bingen: Geistlic-e Ges+ñnge

What am I missing?

GSerg
  • 76,472
  • 17
  • 159
  • 346
Neil Williams
  • 11
  • 2
  • When you paste code, precede the line with 4 spaces, or highlight the code and use the `{}` button in your editor. Consider reading up on [markdown help](https://stackoverflow.com/editing-help) for more information on how to format text on stackoverflow. – JNevill Apr 07 '23 at 18:02
  • I did the same test with variable in Immediate Window and VBA function proc. The "+ñ" is replaced in both. – June7 Apr 07 '23 at 18:19
  • I also tested expression in textbox using field data. Again, characters are replaced. – June7 Apr 07 '23 at 18:25
  • 3
    `What am I missing` - that the VBA IDE [is not Unicode](https://stackoverflow.com/a/25260658/11683). Your `xText` contains *something else*, not what the non-Unicode IDE shows you when you try to display its Unicode value. On contrary, when you are replacing the literal string, that literal string does contain literally the characters you are seeing, because they come from the IDE to begin with. – GSerg Apr 07 '23 at 18:54
  • I copy/pasted "+ñ" from question for use in my testing. The characters from question can be reproduced with `Chr(43) & Chr(241)`. What ASCII codes represent your actual characters in data? Agree with @GSerg, your characters must actually be something else in spite of what is displayed. – June7 Apr 07 '23 at 19:16
  • In this example the letter is `ä` . – Andre Apr 08 '23 at 07:24

2 Answers2

1

There is at least 3 characters that represents "ñ"

  1. UTF-8: 0xC3 0xB1
  2. ISO-8859-1: 0xF1
  3. Windows-1252: 0xF1

So what I suggest you is to isolate the character in this position with mid then store in a variable and get its AscW() and replace by number using ChrW().

it's a long run response, but you will learn the code for future problems

Dim myStr As String, myCha As String, myNewStr As String
Dim ascNum As Long

myStr = "Hildegard Von Bingen: Geistliche Ges+ñnge"
Debug.Print myStr
myCha = Mid(myStr, 38, 1)
ascNum = AscW(myCha)
Debug.Print myCha, ascNum

myNewStr = Replace(myStr, ChrW(ascNum), "n")
Debug.Print myNewStr
June7
  • 19,874
  • 8
  • 24
  • 34
Hudson Komuro
  • 109
  • 2
0

Thanks, guys, for helping me figure out that I wasn't going insane!

I now rebuild the input string xText with a loop swapping each of its characters c for its own Chr(Asc(c)) equivalent. It looks ridiculous in code so I've put a BIG comment in to explain why it's necessary.

The Replace works perfectly now. 99.9% of the time nothing gets changed but it's always the 0.1% that gets you, isn't it?

Neil Williams
  • 11
  • 2
  • 1
    `to explain why it's necessary` - it isn't, and it's broken, as in, will produce different result under different system locales. What you [should](https://meta.stackexchange.com/q/66377/147640) do is to inquire [what encoding](http://www.joelonsoftware.com/articles/Unicode.html) that package expects and give that encoding to it [with `WideCharToMultiByte`](https://stackoverflow.com/a/9093957/11683). – GSerg Apr 08 '23 at 19:39