Handling special characters

Posted on 2009-04-29
Last Modified: 2012-05-06
I'd like to leverage the TranslateChar function to replace a single quote with the appropriate wild card characters need; so that I can use it to insert data to a table.

If I use the TranslateChar function as written on my insert, O'Hara would get entered as OHara. I'd like to keep the ' in the name.  So coll.Add item:="", Key:="'" would need to change to something to use ascii. I added the function in case this question doesn't provide a link back to the original question.

Function TranslateCharx(StrIn As String, StrOut As String) As String

    ' This function may be used in Excel or Access, or in any VB/VBA project.


    ' Function evaluates an ANSI string that may have special characters, identified

    ' in the collection populated below.  If a special character is found, the function

    ' replaces that character with a designated replacement string (may be any number

    ' of characters).  There is no support for Unicode.


    ' The function conserves case, so if the special character is uppercase, then the

    ' first character of the replacement string will be uppercase as well.


    ' While the intent of this function is to "replace" characters with diacritical

    ' marks with their Roman alphabet equivalents (you should feel free to change the

    ' mapping below if you do not think it's right or it does not suit your purposes;

    ' I am no linguist).  However, you could use the code to replace any single ANSI

    ' character with whatever string you desire.


    Dim Counter As Long

    Dim coll As New Collection

    Dim Check As String

    Dim WasLower As Boolean

    Dim Letter As String



    ' This entry is for illustration only!  You should remove before using!

    'coll.Add Item:="dollar", Key:="$"


    ' "special keyboard characters"

    coll.Add item:="", Key:="~"

    coll.Add item:="", Key:="!"

    coll.Add item:="", Key:="@"

    coll.Add item:="", Key:="#"

    coll.Add item:="", Key:="$"

    coll.Add item:="", Key:="%"

    coll.Add item:="", Key:="^"

    coll.Add item:="", Key:="&"

    coll.Add item:="", Key:="*"

    coll.Add item:="", Key:="("

    coll.Add item:="", Key:=")"

    coll.Add item:="", Key:="_"

    coll.Add item:="", Key:="+"

    coll.Add item:="", Key:="-"

    coll.Add item:="", Key:="="

    coll.Add item:="", Key:="`"

    coll.Add item:="", Key:="{"

    coll.Add item:="", Key:="}"

    coll.Add item:="", Key:="|"

    coll.Add item:="", Key:=":"

    coll.Add item:="", Key:=""""

    coll.Add item:="", Key:="<"

    coll.Add item:="", Key:=">"

    'coll.Add item:="", Key:="?"

    coll.Add item:="", Key:=","

    coll.Add item:="", Key:="."

    coll.Add item:="", Key:="/"

    coll.Add item:="", Key:="\"

    coll.Add item:="", Key:=";"

    coll.Add item:="", Key:="'"

    coll.Add item:="", Key:="["

    coll.Add item:="", Key:="]"


    ' Loop through string to look for special characters needing replacement


    For Counter = 1 To Len(StrIn)


        ' Look in collection to see if the current character being considered is a "special"

        ' character


        On Error Resume Next

        Letter = Mid(StrIn, Counter, 1)

        Check = coll(Letter)


        ' Check to see if original character was upper or lower case


        WasLower = (StrComp(Letter, LCase(Letter), vbBinaryCompare) = 0)


        ' If there was no error, that means character was in collection and thus is a

        ' special character needing replacement


        If Err <> 0 Then


            Check = Letter

        End If

        On Error GoTo 0


        ' If character was lower case, return the translation in lower case.  If upper case,

        ' return in proper case (first character capitalized)


        TranslateChar = TranslateChar & IIf(WasLower, LCase(Check), StrConv(Check, vbProperCase))


    StrOut = TranslateChar

    ' Release object variable


    Set coll = Nothing


End Function

Open in new window

Question by:BillTr
    LVL 28

    Expert Comment

       coll.Add Item:=Chr(39), Key:="'"
    for instance?:
    However, since the chr(39) will then be evaluated as an apostrophe by the function, if yuou try to place it in a VBA statement, you'll have the same problem as berfore.
    If I am reading the problem right.
    What is it you are trying to do, in long run?

    Author Comment

    Really I'm just trying to work with names. I have 2 tables. T1 is keyed by user name (with the O'hara). T2, is keyed by something else and has the user names in a delimited string (Charlie Smith, Mike OHara). I need to do a join on the two tables to get the results I need. I've coded a parser that takes names from the second table and builds an associative (cant remember if that's the correct term) table to make the join. T2 has other fields that I need in the join that I won't go into here.

    I would rather keep the ' in the name because that's the proper spelling.

    Perhaps I need to do an if then on the sql directly. I saw an example like that here somewhere but it handled ' on the end of the variable, where this is in the middle. I'm thinking I'm not the first one to deal with this.. there's a lot of O'Hara's in the world...:-D
    LVL 28

    Expert Comment

    Hmm, I would think that is this were a join between 2 tables, we wouldn't have to deal with the vagaries of apostrophes within a vba string
    (Although it could still be done.)
    So table2 has the comma delimited list of names, and it is "Mike Ohara" and not "Mike O'Hara"?
    I still need to see the context, and ID what is not working.  A small db with representative dummy data would help.

    Author Comment

    Maybe if I describe the tables better.

    T1= name,dept,costcenter,status  
    - key'd by name, format includes apostrophy, O'Hara

    T2= Service, description, totalcost, averagecost, HardUsers, SoftUsers...etc
    - key'd by service, which is software or a service from a vendor.
    - HardUsers and SoftUsers is a comma delimited list of names that includes
    the apostrophy. Many Users on each row. Example: John Smith, Mike O'Hara, M Mouse

    T3= name, service
    This is the table I build in my code. I do this by reading all the rows in T2 and
    parsing out the User fields. I do this so I can do a join.
    So, T3 will look like this: Mike OHara, PGP where pgp is the software

    So end query should look like this... which I can now sort by user, by dept, total by costcenter,,,whatever.
    PGP, John Smith, dept1, costcenter1, etc
    PGP, M Mouse, Dept1, costcenter2, etc

    But my code can't deal with the apostrophy. So O'Hara won't make the join.
    I could try it with a Like query I suppose. Or I could add code to take the apostrophy out of the T1 table too, so I'd make the join but have the name wrong.  OHara=OHara

    Hmm.... Or maybe make a new key on T1 that I could leverage later to pull in the right name via a query, instead of code... I think that would work.

    LVL 28

    Expert Comment

    What I am thinking is that you can transform Name in t1 for an instr comparison (where instr(, replace([], chr39, ""))>0
    Select t3.*, t1.* from t3, t1 where instr(, replace([], chr39, ""))>0

    Or something like that.
    I need to reconstruct the tables to fully check out.
    LVL 28

    Accepted Solution

    Hmm, appears to work,  hard coded to just deal with apostrophe in T1 name field, not in T3 name field

    Author Comment

    I found that if I update the T3 table via a recordset it takes the apostrophy. You can see my old sql commented out.

    If !SERVICE <> "" Then
               ' rowNumber = rowNumber + 1
                If rsSERVICE.Fields("PUsers").Value <> "" Then
                PUsers = Nz(rsSERVICE.Fields("PUsers"))
                        UserArray = Split(PUsers, ";")
                        For i = 0 To UBound(UserArray) - 1
                        wsNames = (UserArray(i))
                        With rsUserStage
                        !UserName = wsNames
                        !SERVICE = SERVICE
                        End With
                        'Call TranslateCharx(wsNames, wsNamesx)
                          ' db.Execute "INSERT INTO UserStage(UserName, Service) VALUES ('" & wsNamesx & "', '" & SERVICE & "')"

    Open in new window

    LVL 28

    Expert Comment

    So you need not do the replace in the instr compare between T3 and T1
    i was wondering how you were splitting the T2 field.

    Author Closing Comment

    I think your solution would work so I assigned the points.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now