Prev: Problem with Access 2007 on Windows 7 64-bit
Next: nothing!
From: Mark on 4 Jun 2010 11:43
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
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



--

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

Previous Posts In This Thread:

On Friday, March 05, 2010 4:29 PM
Lori wrote:

Removing non Alpha Numeric Characters
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 is not catching
it because of the non-alpha numeric characters.

What would be the most efficient way of accomplishing this?

On Friday, March 05, 2010 5:30 PM
KARL DEWEY wrote:

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.


"Lori" wrote:

On Friday, March 05, 2010 5:35 PM
Ken Snell wrote:

Define "non-alphanumeric characters"?
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



--

Ken Snell
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/tutorials/aspnet/ef583104-e507-491d-b05f-49faac8854c8/wpf-binding-beyond-the-li.aspx
From: Tom van Stiphout on 5 Jun 2010 17:23
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
<clip>
 | 
Pages: 1
Prev: Problem with Access 2007 on Windows 7 64-bit
Next: nothing!