How to removing non Alpha-numeric characters

[bleeet]

Hi guys

I am new to access and I have made a data base but one of the columns has some non Alpha-numeric characters in each row that I want to remove. The fastest way to do this is to use VBA I guess. Does anyone happen to know how I can do this?


thanks

 

[Joe4]

I was able to adapt the VBA User Defined Function here: Access World Forums - View Single Post - How to get rid of non - alpha-numeric characters? to do that. Here is the code:

Public Function AlphaNumOnly(inputString As Variant) As String


    Dim i As Integer
    Dim myString As String


    If Len(inputString) > 0 Then
        For i = 1 To Len(inputString)
            If Mid(inputString, i, 1) Like "[A-Z,0-9]" Then
                myString = myString & Mid(inputString, i, 1)
            End If
        Next i
    End If


     AlphaNumOnly = myString


End Function

So, if you place this code in a Standard VBA Module in your database, you will be able to use this like any other Access function in a Query calculated field, VBA, etc., i.e.

MyNewString: AlphaNumOnly([TextField])