If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
text control
There's nothing built into Access to do this that I'm aware of.
To do it manually, use the Replace statement multiple times. strText = Replace(strText, ".", "") strText = Replace(strText, ", ", "") strText = Replace(strText, ":", "") etc. or strText = Replace(Replace(Replace(strText, ".", ""), ",", ""), ":", "") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "John Thomas" wrote in message ... Is there a way to Remove all punctuation from a text string, without identifying each type of punctuation, if not what is the best way to do it with the text as a variable. Thanks John |
#2
|
|||
|
|||
text control
Is there a way to Remove all punctuation from a text string, without
identifying each type of punctuation, if not what is the best way to do it with the text as a variable. Thanks John |
#3
|
|||
|
|||
text control
Thanks, I've seen that replace mentioned before, but must after office 97,
which is what I use and is not avaiable to me. I have been playing in msword with find/replace and notice that if I use a wildcard search like [aA-zZ], it will only select text and bypass punctuation. Do you know of a way I could use that method, maybe in a do loop to evalute a variable, stripping it of punctuation. thanks john "Douglas J. Steele" wrote in message ... There's nothing built into Access to do this that I'm aware of. To do it manually, use the Replace statement multiple times. strText = Replace(strText, ".", "") strText = Replace(strText, ", ", "") strText = Replace(strText, ":", "") etc. or strText = Replace(Replace(Replace(strText, ".", ""), ",", ""), ":", "") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "John Thomas" wrote in message ... Is there a way to Remove all punctuation from a text string, without identifying each type of punctuation, if not what is the best way to do it with the text as a variable. Thanks John |
#4
|
|||
|
|||
text control
Sorry: I usually mention that in my replies, since I use Access 97 as well.
You can write your own equivalent function. There's one approach in http://www.mvps.org/access/strings/str0004.htm at "The Access Web". Of course, if you're going to the trouble of writing your own Replace function, you probably should write a PunctuationStripper function, and only call it once. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "John Thomas" wrote in message ... Thanks, I've seen that replace mentioned before, but must after office 97, which is what I use and is not avaiable to me. I have been playing in msword with find/replace and notice that if I use a wildcard search like [aA-zZ], it will only select text and bypass punctuation. Do you know of a way I could use that method, maybe in a do loop to evalute a variable, stripping it of punctuation. thanks john "Douglas J. Steele" wrote in message ... There's nothing built into Access to do this that I'm aware of. To do it manually, use the Replace statement multiple times. strText = Replace(strText, ".", "") strText = Replace(strText, ", ", "") strText = Replace(strText, ":", "") etc. or strText = Replace(Replace(Replace(strText, ".", ""), ",", ""), ":", "") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "John Thomas" wrote in message ... Is there a way to Remove all punctuation from a text string, without identifying each type of punctuation, if not what is the best way to do it with the text as a variable. Thanks John |
#5
|
|||
|
|||
text control
On Sat, 24 Apr 2004 11:50:01 -0400, John Thomas wrote:
Thanks, I've seen that replace mentioned before, but must after office 97, which is what I use and is not avaiable to me. I have been playing in msword with find/replace and notice that if I use a wildcard search like [aA-zZ], it will only select text and bypass punctuation. Do you know of a way I could use that method, maybe in a do loop to evalute a variable, stripping it of punctuation. thanks john "Douglas J. Steele" wrote in message ... There's nothing built into Access to do this that I'm aware of. To do it manually, use the Replace statement multiple times. strText = Replace(strText, ".", "") strText = Replace(strText, ", ", "") strText = Replace(strText, ":", "") etc. or strText = Replace(Replace(Replace(strText, ".", ""), ",", ""), ":", "") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "John Thomas" wrote in message ... Is there a way to Remove all punctuation from a text string, without identifying each type of punctuation, if not what is the best way to do it with the text as a variable. Thanks John In Access 97 you need to write your own User Defined Function. The following will remove the following characters from a string: ! : ; ? . , Add to, or subtract from, the code as wanted. Place this function in a module: Function RemovePunctuation(StringIn As String) As String Dim strNew As String Dim intX As Integer Dim intY As Integer For intX = 1 To Len(StringIn) intY = Asc(Mid(StringIn, intX)) If intY = 33 Or intY = 44 Or intY = 46 Or intY = 58 Or intY = 59 Or intY = 63 Then Else strNew = strNew & Chr(intY) End If Next intX RemovePunctuation = strNew End Function =========== You can call it from a query: Exp:RemovePunctuation([FieldName]) -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#6
|
|||
|
|||
text control
While I was waiting I came up with this procedure, that for the most part,
solves my problem, but I'm a novice and would like to know if it is good or bad programimg. I like that I don't have to guess what is coming up in the text to deal with, except for a few, and that could be a plus. 'with var106 as text variable, following loop strips all punctuation, except for ^_[ ]\ at least as far as I could find var111 = Len(var106) var114 = "" Do Until var111 = 0 If Left(var106, 1) Like "[aA-zZ]" Then var111 = Len(var106) var113 = Left(var106, 1) var114 = var114 & var113 var106 = Mid(var106, 2, var111) Else: var111 = Len(var106) var106 = Mid(var106, 2, var111) End If Loop var106 = var114 ' ends punctuation removal "fredg" wrote in message .. . On Sat, 24 Apr 2004 11:50:01 -0400, John Thomas wrote: Thanks, I've seen that replace mentioned before, but must after office 97, which is what I use and is not avaiable to me. I have been playing in msword with find/replace and notice that if I use a wildcard search like [aA-zZ], it will only select text and bypass punctuation. Do you know of a way I could use that method, maybe in a do loop to evalute a variable, stripping it of punctuation. thanks john "Douglas J. Steele" wrote in message ... There's nothing built into Access to do this that I'm aware of. To do it manually, use the Replace statement multiple times. strText = Replace(strText, ".", "") strText = Replace(strText, ", ", "") strText = Replace(strText, ":", "") etc. or strText = Replace(Replace(Replace(strText, ".", ""), ",", ""), ":", "") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "John Thomas" wrote in message ... Is there a way to Remove all punctuation from a text string, without identifying each type of punctuation, if not what is the best way to do it with the text as a variable. Thanks John In Access 97 you need to write your own User Defined Function. The following will remove the following characters from a string: ! : ; ? . , Add to, or subtract from, the code as wanted. Place this function in a module: Function RemovePunctuation(StringIn As String) As String Dim strNew As String Dim intX As Integer Dim intY As Integer For intX = 1 To Len(StringIn) intY = Asc(Mid(StringIn, intX)) If intY = 33 Or intY = 44 Or intY = 46 Or intY = 58 Or intY = 59 Or intY = 63 Then Else strNew = strNew & Chr(intY) End If Next intX RemovePunctuation = strNew End Function =========== You can call it from a query: Exp:RemovePunctuation([FieldName]) -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#7
|
|||
|
|||
text control
Hi John,
It should be Like "[A-Za-z]" not Like "[aA-zZ] but you may also need to specify numerals and accented characters, e.g. Like "[0-9A-Za-záâçèéêëôûÇ]" However, you can also specify a "negative" character class, e.g. Like "[!a-z]" where the ! is a signal to match all characters that are _not_ listed. So you could use something like Like "[!.,:;!?_-!/\]" to exclude punctuation marks. (There's the slight problem that the Like operator won't let you use ] in a character class because it's needed to signal the end of a class. Here's a slightly neater function that will strip out all the characters except those you specify. Note the way I've declared the variables and given all the variables and arguments meaningful names, which makes the code a great deal easier to understand than with names like var111 and var114. Function StripChars(V As Variant, CharsToKeep As String) As Variant 'CharsToKeep must be a character class for the ' Like operator, e.g. [A-Za-z] Dim C As String * 1 Dim strIn As String Dim strOut As String Dim j As Long If IsNull(V) Then StripChars = Null Exit Function End If strIn = CStr(V) For j = 1 To Len(strIn) C = Mid(strIn, j, 1) If C Like CharsToKeep Then strOut = strOut & C Else strOut = strOut & " " End If Next StripChars = strOut End Function On Sat, 24 Apr 2004 22:50:52 -0400, "John Thomas" wrote: While I was waiting I came up with this procedure, that for the most part, solves my problem, but I'm a novice and would like to know if it is good or bad programimg. I like that I don't have to guess what is coming up in the text to deal with, except for a few, and that could be a plus. 'with var106 as text variable, following loop strips all punctuation, except for ^_[ ]\ at least as far as I could find var111 = Len(var106) var114 = "" Do Until var111 = 0 If Left(var106, 1) Like "[aA-zZ]" Then var111 = Len(var106) var113 = Left(var106, 1) var114 = var114 & var113 var106 = Mid(var106, 2, var111) Else: var111 = Len(var106) var106 = Mid(var106, 2, var111) End If Loop var106 = var114 ' ends punctuation removal "fredg" wrote in message . .. On Sat, 24 Apr 2004 11:50:01 -0400, John Thomas wrote: Thanks, I've seen that replace mentioned before, but must after office 97, which is what I use and is not avaiable to me. I have been playing in msword with find/replace and notice that if I use a wildcard search like [aA-zZ], it will only select text and bypass punctuation. Do you know of a way I could use that method, maybe in a do loop to evalute a variable, stripping it of punctuation. thanks john "Douglas J. Steele" wrote in message ... There's nothing built into Access to do this that I'm aware of. To do it manually, use the Replace statement multiple times. strText = Replace(strText, ".", "") strText = Replace(strText, ", ", "") strText = Replace(strText, ":", "") etc. or strText = Replace(Replace(Replace(strText, ".", ""), ",", ""), ":", "") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "John Thomas" wrote in message ... Is there a way to Remove all punctuation from a text string, without identifying each type of punctuation, if not what is the best way to do it with the text as a variable. Thanks John In Access 97 you need to write your own User Defined Function. The following will remove the following characters from a string: ! : ; ? . , Add to, or subtract from, the code as wanted. Place this function in a module: Function RemovePunctuation(StringIn As String) As String Dim strNew As String Dim intX As Integer Dim intY As Integer For intX = 1 To Len(StringIn) intY = Asc(Mid(StringIn, intX)) If intY = 33 Or intY = 44 Or intY = 46 Or intY = 58 Or intY = 59 Or intY = 63 Then Else strNew = strNew & Chr(intY) End If Next intX RemovePunctuation = strNew End Function =========== You can call it from a query: Exp:RemovePunctuation([FieldName]) -- Fred Please only reply to this newsgroup. I do not reply to personal email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|