Replacing characters (1 Viewer)

[rhbob]

I have a table with a field that has values consisting of a variable number of digits and 3 hyphens between the digits.
(Example: "- -345-4459"). I want to eliminate all the hyphens.
Manually doing a Replace is quite tedious since there are tens of thousands of records and the Replace operation can do only several thousand at a time. Also, I will be getting new sets of records on a regular basis. Is there a way to get rid of the hyphens in a more efficient way, using code or some other method?

 

[Jack Cowley]

Calling this function from an update query should do the trick:

Function StripString(MyStr As Variant) As Variant
On Error GoTo StripStringError

Dim strChar As String, strHoldString As String
Dim i As Integer

' Exit if the passed value is null.
If IsNull(MyStr) Then Exit Function

' Exit if the passed value is not a string.
If VarType(MyStr) <> 8 Then Exit Function

' Check each value for invalid characters.
For i = 1 To Len(MyStr)
strChar = Mid$(MyStr, i, 1)
Select Case strChar
Case "-"
' Do nothing
Case Else
strHoldString = strHoldString & strChar
End Select
Next i

' Pass back corrected string.
StripString = strHoldString

StripStringEnd:
Exit Function

StripStringError:
MsgBox Error$
Resume StripStringEnd

End Function

hth,
Jack