wrong case in the result of Replace Function

I have a small function to screen for French-accented characters and replace them with unaccented English characters. It works OK for uppercase characters, but it replaces a lower-case French character with the upper-case English character.

The specified replacement character IS lower case, so it seems that VBA is making the case decision in spite of what is specified in my code.

I am attaching a small database, ReplaceProblem.accdb with one module in it that shows everything about the problem.

I am also attaching two screen shots of the erroneous results.
ReplaceProblem.accdb
CleanChar-problem.JPG
CleanChar-problem2.JPG
Douglass MacLeanCTOAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Chris MillardConnect With a Mentor Commented:
I've revised the function a little so that the REPLACE function uses vbBinaryCompare. Try this:-

Public Function CleanChar(inString As String) As String
On Error GoTo HandleError
Dim i As Long
Dim CleanTemp As String
    CleanTemp = inString
    For i = 1 To CleanCharMax
            CleanTemp = Replace(CleanTemp, inProbChar(i), outCleanChar(i), , , vbBinaryCompare)
    Next i
    CleanChar = CleanTemp
ExitProc:
    Exit Function
HandleError:
    MsgBox Err.Number & " " & Err.Description & " in CleanChar", vbOKOnly + vbCritical, ModName
    Resume ExitProc
End Function

Open in new window

0
 
Douglass MacLeanCTOAuthor Commented:
Perfect solution. And I learned something important about Replace options.
Thanks much, roybridge!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.