• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 766
  • Last Modified:

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.

Example:
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
            Err.Clear
            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))
    Next
    StrOut = TranslateChar
    ' Release object variable
    
    Set coll = Nothing
    
End Function

Open in new window

0
BillTr
Asked:
BillTr
  • 5
  • 4
1 Solution
 
jerryb30Commented:
   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?
0
 
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
0
 
jerryb30Commented:
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.
0
Technology Partners: 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!

 
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.

0
 
jerryb30Commented:
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
So:
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.
0
 
jerryb30Commented:
Hmm, appears to work,  hard coded to just deal with apostrophe in T1 name field, not in T3 name field
0
 
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
                    .AddNew
                    !UserName = wsNames
                    !SERVICE = SERVICE
                    .Update
                    End With
                    
                    'Call TranslateCharx(wsNames, wsNamesx)
                      ' db.Execute "INSERT INTO UserStage(UserName, Service) VALUES ('" & wsNamesx & "', '" & SERVICE & "')"
                    Next

Open in new window

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now