Query to replace unwanted characters

[John]

Hi

How can I replace all characters except a-b,A-B,0-9 and hyphen from a field
in a table?

Thanks

Regards

 

[Jeff Boyce]

John

Will depend on which version of Access you are using. Does your version
have the Replace() function?

Or, instead of a query, have you tried the find/replace choice from the Edit
menu?

 

[John Spencer]

I would suggest that you need a custom function written in VBA to do this. HEre
is a copy of a function from my library to get only numbers. You can modify
that by changing the strNumbers constant to
Const strNumbers As String = "0123456789AB-"

After copying the code to a module, modifying it, and saving it (module name
must be different than the code modMyFunction), you can call it in your SQL statement.

Field: fStripToNumbersOnly([YourTableName].[yourFieldName])

Or if you are using an update query.
Field: YourFieldName
Criteria: Like "*[!-0123456789AB]*"
Update to: fStripToNumbersOnly([YourTableName].[yourFieldName])

Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles dates, numbers, and strings.
'returns a string

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function