Removing non Alpha Numeric Characters

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

Okay, I have accountants driving me crazy. They are creating duplicate
entries in our accounting program and I would like our database to be able to
pull out blank spaces, commas, dashes, etc. They have a habit of entering an
invoice number as INV-123 or INV 123 and the accounting system isn't catching
it because of the non-alpha numeric characters.

What would be the most efficient way of accomplishing this?
 
You could run an Update query using replace function --
Replace(Replace(Replace([YourField], "-", ""), " ", ""), ",", "")))

You can add on to it with other character.
 
Define "non-alphanumeric characters"? You mean any character that is not A-Z
or 0-9? If yes, this function will do what you seek:



'**************************************
'* *
'* Fxn StripAllNonLetterNumericChars *
'* *
'**************************************

' ** This function strips all nonletter and nonnumeric characters from a
text string.

Function StripAllNonLetterNumericChars(varOriginalString As Variant) As
String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
Dim strOriginalString As String
On Error Resume Next
strTemp = ""
strOriginalString = Nz(varOriginalString, "")
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
If strChar Like "[a-z0-9]" Then blnStrip = False
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonLetterNumericChars = strTemp
Exit Function
End Function
 
Lori said:
Okay, I have accountants driving me crazy. They are creating duplicate
entries in our accounting program and I would like our database to be able
to
pull out blank spaces, commas, dashes, etc. They have a habit of entering
an
invoice number as INV-123 or INV 123 and the accounting system isn't
catching
it because of the non-alpha numeric characters.

What would be the most efficient way of accomplishing this?
Click to expand...
 
Hi this thread is a interesting read, for I need to carry out the same procedure on circa 800k records, what I'm not to sure is how do you call the function, do you do it from within a query

Thanks

Mark



Ken Snell wrote:

Define "non-alphanumeric characters"?
05-Mar-10

Define "non-alphanumeric characters"? You mean any character that is not A-
or 0-9? If yes, this function will do what you seek


'*************************************
'*
'* Fxn StripAllNonLetterNumericChars
'*
'*************************************

' ** This function strips all nonletter and nonnumeric characters from
text string

Function StripAllNonLetterNumericChars(varOriginalString As Variant) A
Strin
Dim blnStrip As Boolea
Dim intLoop As Intege
Dim lngLoop As Lon
Dim strTemp As String, strChar As Strin
Dim strOriginalString As Strin
On Error Resume Nex
strTemp = "
strOriginalString = Nz(varOriginalString, ""
For lngLoop = Len(strOriginalString) To 1 Step -
blnStrip = Tru
strChar = Mid(strOriginalString, lngLoop, 1
If strChar Like "[a-z0-9]" Then blnStrip = Fals
If blnStrip = False Then strTemp = strChar & strTem
Next lngLoo
StripAllNonLetterNumericChars = strTem
Exit Functio
End Functio


-

Ken Snel
http://www.accessmvp.com/KDSnell/

Previous Posts In This Thread:

Removing non Alpha Numeric Characters
Okay, I have accountants driving me crazy. They are creating duplicat
entries in our accounting program and I would like our database to be able t
pull out blank spaces, commas, dashes, etc. They have a habit of entering a
invoice number as INV-123 or INV 123 and the accounting system is not catchin
it because of the non-alpha numeric characters

What would be the most efficient way of accomplishing this?

You could run an Update query using replace function
You could run an Update query using replace function -
Replace(Replace(Replace([YourField], "-", ""), " ", ""), ",", ""))

You can add on to it with other character

-
Build a little, test a little

:

Define "non-alphanumeric characters"?
Define "non-alphanumeric characters"? You mean any character that is not A-
or 0-9? If yes, this function will do what you seek


'*************************************
'*
'* Fxn StripAllNonLetterNumericChars
'*
'*************************************

' ** This function strips all nonletter and nonnumeric characters from
text string

Function StripAllNonLetterNumericChars(varOriginalString As Variant) A
Strin
Dim blnStrip As Boolea
Dim intLoop As Intege
Dim lngLoop As Lon
Dim strTemp As String, strChar As Strin
Dim strOriginalString As Strin
On Error Resume Nex
strTemp = "
strOriginalString = Nz(varOriginalString, ""
For lngLoop = Len(strOriginalString) To 1 Step -
blnStrip = Tru
strChar = Mid(strOriginalString, lngLoop, 1
If strChar Like "[a-z0-9]" Then blnStrip = Fals
If blnStrip = False Then strTemp = strChar & strTem
Next lngLoo
StripAllNonLetterNumericChars = strTem
Exit Functio
End Functio


-

Ken Snel
http://www.accessmvp.com/KDSnell/


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Binding Beyond the Limitation of Name Scopes
http://www.eggheadcafe.com/tutorial...f-49faac8854c8/wpf-binding-beyond-the-li.aspx
 
On Fri, 04 Jun 2010 08:43:06 -0700, Mark Whyte wrote:

Yes you can. Here is a sample query:
select myField, StripAllNonLetterNumericChars(myField)
from myTable

Of course you can use an Update query as well.

-Tom.
Microsoft Access MVP

Hi this thread is a interesting read, for I need to carry out the same procedure on circa 800k records, what I'm not to sure is how do you call the function, do you do it from within a query

Thanks

Mark



Ken Snell wrote:

Define "non-alphanumeric characters"?
05-Mar-10

Define "non-alphanumeric characters"? You mean any character that is not A-Z
or 0-9? If yes, this function will do what you seek:



'**************************************
'* *
'* Fxn StripAllNonLetterNumericChars *
'* *
'**************************************

' ** This function strips all nonletter and nonnumeric characters from a
text string.

Function StripAllNonLetterNumericChars(varOriginalString As Variant) As
String
Click to expand...
<clip>
 
Back
Top