Search and Replace 2

[Freckles]

Is there anyway that I can do a search and replace with a query? I need to remove all NON-alpha/numeric characters.

Example: 456-444*777 to 456444777



: Deb Koplen
deb.koplen@verizon.com
koplend@swbell.net (weekends and nights)

A person can stand almost anything except a succession of ordinary days.

 

[PHV]

You may consider creating a Public Function in a standard module, like this:

Public Function myCleanUp(myField)
  Dim i As Long, x As String, s As string
  If Len(Trim(Nz(myField, ""))) = 0 Then
    myCleanUp = myField
    Exit Function
  End If
  s = ""
  For i = 1 To Len(myField)
    x = Mid(myField, i, 1)
    If (UCase(x) >= "A" And UCase(x) <= "Z") _
    Or (x >= "0" And x <= "9") Or x = " " Then
      s = s & x
    End If
  Next i
  myCleanUp = s
End Function

And then, you may do an Update query, like this:
UPDATE theTable SET theField=myCleanUp(theField);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244