Solved

wrong case in the result of Replace Function

Posted on 2013-05-16
2
431 Views
Last Modified: 2013-05-17
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
0
Comment
Question by:Douglass MacLean
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 17

Accepted Solution

by:
Chris Millard earned 500 total points
ID: 39173972
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
 

Author Closing Comment

by:Douglass MacLean
ID: 39174061
Perfect solution. And I learned something important about Replace options.
Thanks much, roybridge!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

751 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