Not supported Replace() function

[jane30]

Hi, I tested the following code in debug window in Access 97: Mystring="Hello"
? replace(mystring, "No way&quot

It returned me an error message:
Run-time error: 438
Object doesn't support this property or method.

My Access 97 was installed from Microsoft Office 97 Professional Edition. In the Help Index, I use "Find" option to location "Replace", I found no Replace() function. Does it mean Access 97 doesn't support Replace() function? If no, how can I get Replace() function work? Please help, thanks a bunch in advance.

 

[Sdaddy]

I experienced something similar in Access 2000 with trying to call a particular database and to import .txt files into tables. I could not find the setdatabase command.

You will need to check your references under the tools options and pick the correct library that contains the set of commands you are looking for.
This is a very common problem. Hope this helps

 

[JohnYingling]

Office '97 VBA has a VB 5 base which does not have the Replace, Split and Join functions among other features. Office 2000 has a VB6 base which has the functions. Do a search, there are examples of a Replace function written in VB5.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[jiqjaq]

This is not the most efficient way, but it works:

Public Function StrReplace(s As String, sFind As String, sReplace As String) As String
' s is the original string
' sFind is the string to be replaced
' sReplace is the replacement for SFind
Dim leng As Integer
Dim pos As Integer
leng = Len(sFind)
StrReplace = s
Do
pos = InStr(StrReplace, sFind)
If pos > 0 Then
StrReplace = Left$(StrReplace, pos - 1) & sReplace & Mid$(StrReplace, pos + leng)
Else
Exit Do
End If
Loop
End Function

-----
If you have this function you can do:
?strreplace("No Way Jose, absolutely No Way to do it","Way","Can Do&quot
No Can Do Jose, absolutely No Can Do to do it

 

[SBendBuckeye]

I wrote this when I had to make a system backwards compatible from Access2000 to Access97. The optional parms relate to the Access2000 replace statement so you can do a global replace of Replace with MyReplace without worrying about ripple effects.

Public Function MyReplace(pstrText As String, pstrSearch _
As String, Optional ostrReplace As String = vbNullString, _
Optional o3, Optional o4, Optional o5, Optional o6) As String
Dim strleft As String
Dim strRight As String
Dim strTemp As String
Dim strText As String
Dim strSearch As String
Dim intWhere As Integer

strText = UCase$(pstrText)
strSearch = UCase$(pstrSearch)
intWhere = InStr(strText, strSearch)

'If search string not found then return original string
If intWhere = 0 Then
MyReplace = pstrText
Else
strleft = Left$(pstrText, intWhere - 1)
strRight = Mid$(pstrText, intWhere + Len(pstrSearch))
strTemp = strleft & ostrReplace & strRight
MyReplace = strTemp
End If

'.Tag = myreplace(.Tag, "AlwaysHidden;", vbNullString, , , vbDatabaseCompare)

End Function

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!