Need to remove all punctuation

  • Thread starter Thread starter Bonnie A
  • Start date Start date
B

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.
 
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

Bonnie said:
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.
Click to expand...
 
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
..
 
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


raskew via AccessMonster.com said:
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

Bonnie said:
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.
Click to expand...
Click to expand...
 
Bonnie A said:
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 "*/*"
Click to expand...

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
 
Back
Top