Need to remove all punctuation

[Bonnie A]

Hi everyone! Using A02 on XP. I have a file that I prepare for populating
1099 forms and one part of my prep is to capitalize the fields and remove all
punctuation. I import the data to identify all the characters I can think of
but usually there is something weird that kicks out later and I add it to my
list of what to find/replace with Null next year.

Like "*,*" Or Like "*.*" Or Like "*-*" Or Like "*'*" Or Like "*;*" Or Like
"*:*" Or Like "*/*"

My query pulls [FName], [MI], and [LName] with the above on the first,
second and third criteria lines.

Then I get to do a ReplaceAll on each column for each character. I refresh
the query periodically until nothing meets my criteria.

I tried an update query but just blanked out the entire FName, MI and LName
on all the records that met the above criteria.

I'm pretty sure I could write something instead of the Like statement that
would identify all non-alpha characters. But still don't know how to remove
just those non-alphas.

I know there's probably a simple way to do it but can't figure it out. I
would appreciate any help or advice. Thank you very much for taking the time
to help out.

 

[raskew via AccessMonster.com]

Hi -

This pair of functions might give you a starting point:

Function SaveAlpha(ByVal pstr As String) As String
'*******************************************
'Purpose: Removes non-alpha characters from
' a string
'Coded by: raskew
'Calls: Function IsAlpha()
'Inputs: ? SaveAlpha(" t#he *qu^i5ck !b@r#o$w&n fo#x ")
'Output: the quick brown fox
'Note: As written, empty spaces are ignored.
'*******************************************

Dim strHold As String
Dim intLen As Integer
Dim n As Integer

strHold = Trim(pstr)
intLen = Len(strHold)
n = 1
Do
If Mid(strHold, n, 1) <> " " And Not IsAlpha(Mid(strHold, n, 1)) Then
strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
n = n - 1
End If
n = n + 1
Loop Until Mid(strHold, n, 1) = ""
SaveAlpha = strHold

End Function
Function IsAlpha(strIn As String) As Boolean
'*******************************************
'Purpose: Determine if a character is alpha
' i.e. "a" - "z" or "A" - "Z"
'Coded by: raskew
'Inputs: ? IsAlpha("4"),
'Output: False
'*******************************************

Dim i As Integer

i = Switch(Asc(strIn) > 122 Or Asc(strIn) < 65, 1, _
InStr("91 92 93 94 95 96", Asc(strIn)) > 0, 2, _
True, 3)
IsAlpha = IIf(i = 3, True, False)

End Function

HTH - Bob

Hi everyone! Using A02 on XP. I have a file that I prepare for populating
1099 forms and one part of my prep is to capitalize the fields and remove all
punctuation. I import the data to identify all the characters I can think of
but usually there is something weird that kicks out later and I add it to my
list of what to find/replace with Null next year.

Like "*,*" Or Like "*.*" Or Like "*-*" Or Like "*'*" Or Like "*;*" Or Like
"*:*" Or Like "*/*"

My query pulls [FName], [MI], and [LName] with the above on the first,
second and third criteria lines.

Then I get to do a ReplaceAll on each column for each character. I refresh
the query periodically until nothing meets my criteria.

I tried an update query but just blanked out the entire FName, MI and LName
on all the records that met the above criteria.

I'm pretty sure I could write something instead of the Like statement that
would identify all non-alpha characters. But still don't know how to remove
just those non-alphas.

I know there's probably a simple way to do it but can't figure it out. I
would appreciate any help or advice. Thank you very much for taking the time
to help out.

 

[John Spencer]

You could try the following if you wanted to show records that had any
character other then A to z.

Match if any character is not A to Z
Like "*[!A-z]*"

Or

Like "*[#-/;:<=>]*"

OR list the individual characters you want to locate (special case list the
dash as the first character and the exclamation CANNOT be the first
character..
LIKE "*[-,.;/'!]*"

The replace operation could be done using a function. You would need to
define which characters you wanted to replace (or which you wanted to keep).

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Bonnie A]

Hi raskew! Thanks a bunch for the fast response.

The code looks great but I'm not clear how to use it. Do I create a
continuous form to display all the records in my table and have the code run
OnClick?

Thanks for your help!
--
Bonnie W. Anderson
Cincinnati, OH

Hi -

This pair of functions might give you a starting point:

Function SaveAlpha(ByVal pstr As String) As String
'*******************************************
'Purpose: Removes non-alpha characters from
' a string
'Coded by: raskew
'Calls: Function IsAlpha()
'Inputs: ? SaveAlpha(" t#he *qu^i5ck !b@r#o$w&n fo#x ")
'Output: the quick brown fox
'Note: As written, empty spaces are ignored.
'*******************************************

Dim strHold As String
Dim intLen As Integer
Dim n As Integer

strHold = Trim(pstr)
intLen = Len(strHold)
n = 1
Do
If Mid(strHold, n, 1) <> " " And Not IsAlpha(Mid(strHold, n, 1)) Then
strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
n = n - 1
End If
n = n + 1
Loop Until Mid(strHold, n, 1) = ""
SaveAlpha = strHold

End Function
Function IsAlpha(strIn As String) As Boolean
'*******************************************
'Purpose: Determine if a character is alpha
' i.e. "a" - "z" or "A" - "Z"
'Coded by: raskew
'Inputs: ? IsAlpha("4"),
'Output: False
'*******************************************

Dim i As Integer

i = Switch(Asc(strIn) > 122 Or Asc(strIn) < 65, 1, _
InStr("91 92 93 94 95 96", Asc(strIn)) > 0, 2, _
True, 3)
IsAlpha = IIf(i = 3, True, False)

End Function

HTH - Bob

Hi everyone! Using A02 on XP. I have a file that I prepare for populating
1099 forms and one part of my prep is to capitalize the fields and remove all
punctuation. I import the data to identify all the characters I can think of
but usually there is something weird that kicks out later and I add it to my
list of what to find/replace with Null next year.

Like "*,*" Or Like "*.*" Or Like "*-*" Or Like "*'*" Or Like "*;*" Or Like
"*:*" Or Like "*/*"

My query pulls [FName], [MI], and [LName] with the above on the first,
second and third criteria lines.

Then I get to do a ReplaceAll on each column for each character. I refresh
the query periodically until nothing meets my criteria.

I tried an update query but just blanked out the entire FName, MI and LName
on all the records that met the above criteria.

I'm pretty sure I could write something instead of the Like statement that
would identify all non-alpha characters. But still don't know how to remove
just those non-alphas.

I know there's probably a simple way to do it but can't figure it out. I
would appreciate any help or advice. Thank you very much for taking the time
to help out.

 

[Keith Wilby]

Hi everyone! Using A02 on XP. I have a file that I prepare for
populating
1099 forms and one part of my prep is to capitalize the fields and remove
all
punctuation. I import the data to identify all the characters I can think
of
but usually there is something weird that kicks out later and I add it to
my
list of what to find/replace with Null next year.

Like "*,*" Or Like "*.*" Or Like "*-*" Or Like "*'*" Or Like "*;*" Or Like
"*:*" Or Like "*/*"

Here's some code I wrote that you might be able to adapt. Paste it into a
module and run it from the immediate window. You may have to run it more
than once if there are a lot of offending characters. There may well be a
more elegant method but this works for me.

Keith.
www.keithwilby.com

Sub libReplaceWildcards()

'Author: Keith Wilby
'Date: 13 May 2005
'Purpose: To rid fields of wildcard characters using stored update queries
'Calls: libWildcards
'Returns: None

Dim strLegend As String 'MsgBox legend
intRecordCount = 0

Call libWildcards("&", "MyField", "qryMyQuery") 'Call the routine for "&" in
the drawing title
Call libWildcards("%", "MyField", "qryMyQuery") 'Call the routine for "%" in
the drawing title
Call libWildcards("#", "MyField", "qryMyQuery") 'Call the routine for "#" in
the drawing title
Call libWildcards("*", "MyField", "qryMyQuery") 'Call the routine for "*" in
the drawing title
Call libWildcards("?", "MyField", "qryMyQuery") 'Call the routine for "?" in
the drawing title
Call libApostrophes(Chr(39), "MyField", "qryMyQuery") 'Call the routine for
" ' " in the drawing title

If intRecordCount <> 1 Then
strLegend = " records updated."
Else
strLegend = " record updated."
End If

MsgBox intRecordCount & strLegend, vbInformation, "Wildcard removal."

End Sub

Sub libWildcards(strSearchChar As String, strField As String, strTable As
String)

'Author: Keith Wilby
'Date: 17 May 2005
'Called from: libReplaceWildcards
'Purpose: remove wildcards from drg titles

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String, intPosition
As Integer
Dim strSearchString As String, strNewString As String, strReplaceChar As
String
Set db = CurrentDb

If strSearchChar = "&" Then strReplaceChar = "AND"
If strSearchChar = "%" Then strReplaceChar = " PERCENT"
If strSearchChar = "*" Then strReplaceChar = "x"
If strSearchChar = "?" Then strReplaceChar = "q"

strSQL = "Select " & strField & " From " & strTable & " Where " & strField &
" Like '*[" & strSearchChar & "]*'"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then GoTo ExitSub

With rs
.MoveFirst
Do Until .EOF
strNewString = rs(strField)
strNewString = Replace(strNewString, strSearchChar, strReplaceChar)
.Edit
rs(strField) = strNewString
.Update
Debug.Print strNewString
intRecordCount = intRecordCount + 1 'Increment the count
.MoveNext
Loop
End With

ExitSub:
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Sub libApostrophes(strSearchChar As String, strField As String, strTable As
String)

'Author: Keith Wilby
'Date: 27 May 2005
'Called from: libReplaceWildcards
'Purpose: remove apostrophes from drg titles

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String, intPosition
As Integer
Dim strSearchString As String, strNewString As String
Set db = CurrentDb

strSQL = "Select " & strField & " From " & strTable & " Where InStr(nz([" &
strField & "]),""" & strSearchChar & """)>0"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then GoTo ExitSub

With rs
.MoveFirst
Do Until .EOF
strNewString = rs(strField)
strNewString = Replace(strNewString, strSearchChar, "")
.Edit
rs(strField) = strNewString
.Update
Debug.Print strNewString
intRecordCount = intRecordCount + 1 'Increment the count
.MoveNext
Loop
End With

ExitSub:
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub