Query to replace unwanted characters

Hi

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

Thanks

Regards

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?

--
Regards

Jeff Boyce
Office/Access MVP

"John" wrote in message
...
Hi

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

Thanks

Regards

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



Jeff Boyce wrote:

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?

--
Regards

Jeff Boyce
Office/Access MVP

"John" wrote in message
...
Hi

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

Thanks

Regards