[AccessD] Re: Remove Punctuation

Greg,

Try this function.  It seems to work on everything I could throw at it.

*****  Watch for word wrap  *****


Function ReplaceChar(InputString As String) As String
     Dim intLen As Integer
     Dim intCounter As Integer
     'First, remove all carriage returns/linefeeds from the string
     InputString = Replace(InputString, vbCrLf, " ")
     'Find the length of the remaining string now that CR/LF are removed
     intLen = Len(InputString)
     'Now loop through each remaining character in the string, and if it is 
a control
     'character (ASCII value less than 32 and > 32 but < 47), then replace 
that character with a space
     For intCounter = 1 To intLen
         If Asc(Mid(InputString, intCounter, 1)) < 32 Then
             InputString = Replace(InputString, Mid(InputString, 
intCounter, 1), "")
         End If
     Next intCounter
     intLen = Len(InputString)
     For intCounter = 1 To intLen - 1
         If Asc(Mid(InputString, intCounter, 1)) > 32 And 
Asc(Mid(InputString, intCounter, 1)) < 47 Then
             InputString = Replace(InputString, Mid(InputString, 
intCounter, 1), "")
         End If
     Next intCounter
     'Return the input string, which has now had all control charcters
     'converted into spaces
     ReplaceChar = InputString
End Function

Robert

At 12:00 PM 1/22/2004 -0600, you wrote:
>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