For '97:
Public Function Replace(ByVal Expression As String, ByVal Find As String,
ByVal ReplaceBy As String, Optional ByVal Start As Long = 1, Optional ByVal
Count As Long = -1, Optional Method As Long = VBA.vbBinaryCompare) As String
Dim Pos As Long, Cnt As Long
Dim FindLen As Long
Dim ReplLen As Long
Dim SkipLen As Long
On Error GoTo ERR_REPLACE
Replace = Expression
FindLen = VBA.Len(Find)
ReplLen = VBA.Len(ReplaceBy)
SkipLen = VBA.IIf(ReplLen > FindLen, ReplLen, FindLen)
Pos = VBA.InStr(Start, Expression, Find, Method)
While Pos > 0 And (Cnt < Count Or Count < 1)
Start = Pos
Expression = VBA.Left$(Expression, Start - 1) & ReplaceBy &
VBA.Mid$(Expression, Start + FindLen)
Start = Pos + SkipLen
Cnt = Cnt + 1
Pos = VBA.InStr(Start, Expression, Find, Method)
Wend
EX_REPLACE:
Replace = Expression
Exit Function
ERR_REPLACE:
Resume EX_REPLACE
End Function
HTH
Pieter
fredg said:
How do I create a query to find and delete comma's in a table?
Permanently delete all the commas?
What version of Access?
Later Access 2000 or newer?
Update YourTable Set YourTable.[FieldName] =
Replace([FieldName],",","");
Access 97?
Post back. You'll need to write a User Defined function.
