Removal of all special charcaters from Access database fields automaticallyVBA code

[nikhil007]

It is one of the challenging problems that we are facing. We have a access table containing customer details and records to the tune of about 16-17 lacs.
Problem is that details of customer in access contains special characters.

Below attached code does not give me the option to run it in access, as also in excel,since it is run using a userdefined custom defined function in excel sheet on a single cell. It is not working as a macro.

What i want is that below code with modifications should run in access as a VBA code on all selected fields like customer name and father name in database automatically and shall remove all special charcaters in database and generate query containing records of all those with special characters.

Any help on this is highly appreciated. Any other info if required, kindly let me know.

Rgds
Nikhil
================================================== =========================================

Function CleanSpecialChars( ByVal varField as Variant) As Variant

Dim strSpecialChars As String
Dim intLen as Integer
Dim intCounter As Integer

strSpecialChars = "<>{}[]*&^%#@!~^$" '<== //TODO: Add to this string all characters
' you want to remove (do not include comma(,))
intLen = Len(strSpecialChars)

if Not IsNull(varField) Then
intCounter = 1
While intCounter <= intLen
' remove special chars
varField = Replace(varField, Mid(strSpecialChars, intCounter, 1), "")
intCounter = intCounter + 1
Wend
End If
CleanSpecialChars = varField


End Function

[alansidman]

This might be helpful

http://www.utteraccess.com/forum/rem...-t1596744.html

[orange]

If you want only alpha and numeric characters, you could work with the code at
http://access.mvps.org/access/strings/str0002.htm

You'll have to adjust to allow only a-z and A-Z and 0-9

but I'd be cautious in trying to identify all the "special characters" and then remove them.

Just a thought.