[AccessD] Remove Punctuation

Greg,

I'm sure there's other functions out there to do this but here's the code
from one I wrote a long time ago.  It may not be pretty but it works.  Using
your example, you would call it like this: strMyNewString=CleanChars("Smith,
Greg W.",",.","N")

Function CleanChars(strMyString As String, strSearchString As String,
strSpace As String)
'This function strips strSearchString characters from strMyString
'strSearchString can be one character long or several characters long
'This function will search for each individual character of strSearchString
in strMyString

Dim strTemp As String
Dim strNew As String
Dim strSearchChar As String
Dim intWhereChar As Integer
Dim X As Long

strNew = strMyString

For X = 1 To Len(strSearchString)
    strSearchChar = Mid(strSearchString, X, 1)
    intWhereChar = InStr(1, strNew, strSearchChar)

    If strSpace = "Y" Or strSpace = "y" Then
        'This Loop replaces strSearchString with a space
        Do Until intWhereChar = 0
            strTemp = Left(strNew, intWhereChar - 1) & " " & Right(strNew,
Len(strNew) - intWhereChar)
            strNew = strTemp
            intWhereChar = InStr(1, strNew, strSearchChar)
        Loop
    Else
        'This Loop removes SearchStr and does NOT replace it with a space
        Do Until intWhereChar = 0
            strTemp = Left(strNew, intWhereChar - 1) & Right(strNew,
Len(strNew) - intWhereChar)
            strNew = strTemp
            intWhereChar = InStr(1, strNew, strSearchChar)
        Loop
    End If
   
Next X

CleanChars = strNew
End Function



-----Original Message-----
From: Greg Smith [mailto:weeden1949 at hotmail.com]
Sent: Thursday, January 22, 2004 10:57 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Remove Punctuation


Hello everyone!  Greetings from Frigid Iowa.

Access 97.

I need to remove punctuation from a single field in a table which has about
1,500,000 records in it...so far.  It's only 4 columns wide, but fairly
lengthy.

A client has to have all punctuation removed from this particular field,
which is a text field.  I have code which removes the punctuation (as Smith,
Greg W. becomes Smith  Greg W ) but leaves a space in it's place.  Yes, it
gets rid of the punctuation, but for future consistancy (from now on, they
are putting the data in without any punctuation) I can't have the additional
space where the "," or "." was or the search routines will not find every
instance of, for example, "Smith Greg W" because "Smith  Greg W " isn't the
same.

The code I'm using came from M$'s support, and it does work for removing the
characters that I want to remove.  However, if I use "" instead of " ",
nothing gets changed.  It's "ACC: Sample Function to Replace Special
Characters", article #109825.

Is there a better way to do this?  I remember doing this several years ago
when I was first setting up their data, but it's been waaaaay too many moons
since then.

Any help would be greatly appreciated!

TIA!

Greg Smith
Weeden1949 at hotmail.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com