Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

wrong case in the result of Replace Function

Posted on 2013-05-16
2
Medium Priority
?
436 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
2 Comments
 
LVL 17

Accepted Solution

by:
Chris Millard earned 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 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