text control

[Douglas J. Steele]

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, ".", ""), ",", ""), ":", "")

 

[John Thomas]

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

 

[John Thomas]

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]

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

(No private e-mails, please)

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

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, ".", ""), ",", ""), ":", "")

do

it

 

[fredg]

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

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, ".", ""), ",", ""), ":", "")

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])

 

[John Thomas]

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

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

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

(No private e-mails, please)



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])

 

[John Nurick]

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

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

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

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

(No private e-mails, please)



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])