Handling special characters

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

Who is Participating?
Hmm, appears to work,  hard coded to just deal with apostrophe in T1 name field, not in T3 name field
   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?
BillTrAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
BillTrAuthor Commented:
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.

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

Or something like that.
I need to reconstruct the tables to fully check out.
BillTrAuthor Commented:
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

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.
BillTrAuthor Commented:
I think your solution would work so I assigned the points.
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.