Delete Space (A97)

  • Thread starter Thread starter frank
  • Start date Start date
F

frank

I have the same problem as the guy below who wants to remove spaces from
multiple records, except I need to do it in Access 97. I've already tried
the Replace function, but it says "Undefined Function 'Replace' in
expression". Any ideas how I can do it?
 
On my website, see sig below, is a small sample database called
"StringStuff.mdb" which has a ReplaceCharacter function I wrote for this.

Function ReplaceCharacter(Target As String, SearchChar, ReplaceChar) As
String
Dim i As Integer
Dim tempstring As String

For i = 1 To Len(Target)
If Mid(Target, i, 1) = SearchChar Then
tempstring = tempstring & ReplaceChar
Else
tempstring = tempstring & Mid(Target, i, 1)
End If
Next i
ReplaceCharacter = tempstring
End Function

Called like this:
stringvar = ReplaceCharacter(Target, SearchChar, ReplaceChar)

There is also a ReplaceString function which works for whole strings rather
than just characters. If you download the samples, you can see how they
work.
 
Frank, try pasting something like this into a standard module in A97:

Function Replace(strExpr As String, strFind As String, _
strReplace As String, Optional lngStart As Long = 1) As String
Dim strOut As String
Dim lngLenExpr As Long
Dim lngLenFind As Long
Dim lng As Long

lngLenExpr = Len(strExpr)
lngLenFind = Len(strFind)

If (lngLenExpr > 0) And (lngLenFind > 0) And (lngLenExpr >= lngStart)
Then
lng = lngStart
If lng > 1 Then
strOut = Left$(strExpr, lng - 1)
End If
Do While lng <= lngLenExpr
If Mid(strExpr, lng, lngLenFind) = strFind Then
strOut = strOut & strReplace
lng = lng + lngLenFind
Else
strOut = strOut & Mid(strExpr, lng, 1)
lng = lng + 1
End If
Loop
Replace = strOut
End If
End Function
 
Back
Top