REPLACE function in Access 97??

[dt...@yahoo.com]

All:

I am using both Access 97 and 2000.
In Access 2000, I could use the REPLACE() function
in the VBA modules.

But in Access 97, it does not recognize it as a
function. I'd like to use this function in
my code in Access 97. HELP! HELP!

Sent via Deja.com http://www.deja.com/
Before you buy.

[Douglas J. Steele]

It could have a lot to do with the fact that the Replace function is not
part of the functionality of Access 97.

If you need this same functionality in Access 97, you'll have to write your
own "Replace" function.

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/

<dt...@yahoo.com> wrote in message news:8h423e$ro6$1...@nnrp1.deja.com...

[Terry Kreft]

The following seems to follow the same rules as the A2k Replace function as
documented (except the ability to pass vbUseCompareOption as the Compare
parameter)

'*********** Code Start ***********
Public Function Replace(Expression As String, Find As String, ReplaceWith As
String, _
Optional Start As Integer = 1, Optional Count As
Integer = -1, _
Optional Compare As Integer = vbBinaryCompare)

'*******************************************
'Name: Replace (Function)
'Purpose: Access97 version of the A2k Replace function
'Author: Terry Kerft
'Date: June 01, 2000, 02:44:34
'Called by: Any
'Calls: None
'Inputs: Expression - String to Search
' Find - Sub-String to be replaced
' ReplaceWith - Sub-String to insert
' Start - Optional start of string to return and replace
' Count - Optional number of replacements to carry out
' Compare - Optional compare method to use _
(valid values are 0, 1, 2)
'Output: 1) If Expression is zero-length then a zero-length string ("")
' 2) If Expression is Null then an error
' 2) If Find is zero-length then a copy of Expression.
' 3) If ReplaceWith is zero-length then a copy of _
Expression with all occurences of find removed.
' 4) If Start > Len(Expression) then a zero-length string.
' 5) If Count is 0 then a copy of Expression.
'*******************************************

Dim intInstr As Integer
Dim intCount As Integer
Dim intFindLen As Integer
Dim intRepLen As Integer
Dim strRet As String
Dim strTemp As String
On Error GoTo Replace_err

intFindLen = Len(Find)
intRepLen = Len(ReplaceWith)

If Compare < vbBinaryCompare Or Compare > vbDatabaseCompare Then
Err.Raise 1 + vbObjectError, description:="Bad compare method"
ElseIf Len(Expression) = 0 Or Start > Len(Expression) Then
strRet = ""
ElseIf Count = 0 Or intFindLen = 0 Then
strRet = Expression
Else
strTemp = Mid(Expression, Start)
intCount = Count
intInstr = InStr(1, strTemp, Find, Compare)
Do While intInstr > 0
strRet = strRet & Left(strTemp, intInstr - 1) & ReplaceWith
strTemp = Mid(strTemp, intInstr + Len(Find))
intInstr = InStr(1, strTemp, Find, Compare)
intCount = intCount - 1
If intCount = 0 Then Exit Do
Loop
End If
strRet = strRet & strTemp
Replace_end:
Replace = strRet
Exit Function
Replace_err:
strRet = ""
With Err
.Raise .Number, .Source, .description, .HelpFile, .HelpContext
End With
End Function
'*********** Code End ***********