?? can not understand how to apply this. . help

[EBee]

i just need a few more detail about this FAQ posting:

Function ReplaceString(ByVal SourceString As String, ByVal OriginalString As String, ByVal NewString As String) As String

'Recursive function ReplaceString searches Source string and replaces ALL OCCURRENCES of OriginalString with NewString.
'If a value for NewString is ommitted (or IsEmpty), then all occurrences of OriginalString are removed from the SourceString!

Dim Position As Integer

If SourceString = "" Or IsNull(SourceString) Then
ReplaceString = SourceString
Else
Position = InStr(1, SourceString, OriginalString)
If Position > 0 Then
ReplaceString = (Mid$(SourceString, 1, Position - 1) & NewString & ReplaceString(Mid(SourceString, Position + Len(OriginalString)), OriginalString, NewString))
Else
ReplaceString = SourceString
End If
End If

End Function


If i have a table called AddressP and a field called ADDRESS_1 and wanted to search the word Avenue and replace it it with Ave using VBA( Code Above) what do i need to change. really confused on what to change to the sample code for it to work.

And is it possible to use Private Sub instead of a function. . . really don't understand how to use the function. . Private sub you just run it from the module and it runs. . . but when you run a function in the module. . what do you need to do to run it. . .

thanks
erwin

 

[MichaelRed]

I would suggest that the above is extreme overkill for your purpose. Check HELP for the REPLACE function. It should be perfectly suited to your needs -and a bit easier to understand, esp. with the formal doc.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[EBee]

i am using Access97 and the replace function is not available.. only on 2000+. I would like to use vba to do multiple replace on about 10 items. it would be a lot easier to hard code these changes so i don't have to do a search and replace 10+ times for 80,000 records. I just need to understand how the search and replace works so i can code it to do all may changes at the same time.

thanks

 

[B827]

Make a copy of your data in case of disaster.

If you open the table, select the field, then use the
Edit menu and select
Replace
Put in Your two text strings, and ensure that only search current field. In this case you will have to uncheck the Match Whole Field option. Then do the replacing. You get the opportunity to reject the changes. This is much easier than code for one off changes, as MichaelRed says. Sandy

 

[dsb]

Even if you decided to code it, there are simpler and higher performance ways than using recursion.

Function sReplace(sSource As String, _
sFind As String, _
sWith As String, _
Optional ByVal vbTypeCompare) As String

Dim lngPos As Long

sReplace = sSource
If IsMissing(vbTypeCompare) Then _
vbTypeCompare = vbTextCompare

lngPos = InStr(1, sSource, sFind, vbTypeCompare)

Do Until lngPos = 0
sReplace = Left$(sReplace, lngPos - 1) & _
sWith & _
Mid$(sReplace, lngPos + Len(sFind))
lngPos = InStr(lngPos + Len(sWith), _
sReplace, sFind, vbTypeCompare)
Loop

End Function