loop on replace function

[Guest]

//i use the following code on a module to create a 'replace' function:

Function Replace(ByVal Valuein As String, ByVal WhatToReplace As _
String, ByVal Replacevalue As String) As String
Dim Temp as String, P As Long
Temp = Valuein
P = Instr(Temp, WhatToReplace)
Do While P > 0
Temp=Left(Temp, P-1) & Replacevalue & _
Mid(Temp, P+Len(WhatToReplace))
P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
Loop
Replace = Temp
End Function

//the 'replace' function is then used on an update query as follow:
UPDATE Table1.field1 = Replace([Table1]![field1],"Î","ö");

//the above works ok to replace one character within a string.

//i then tried to change the module so that the 'replace' function could be
used to change more than 1 character:
Function replace(ByVal Valuein As String, ByVal WhatToReplace1 As _
String, ByVal Replacevalue1 As String, ByVal
WhatToReplace2 As _
String, ByVal Replacevalue2 As String) As String
Dim Temp As String, P1, P2 As Long
Temp = Valuein
P1 = InStr(Temp, WhatToReplace1)
P2 = InStr(Temp, WhatToReplace2)

Do While P1 > 0 And P2 > 0
'first replace
Temp = Left(Temp, P1 - 1) & Replacevalue1 & _
Mid(Temp, P1 + Len(WhatToReplace1))
P1 = InStr(P1 + Len(Replacevalue1), Temp, WhatToReplace1, 1)
'second replace
Temp = Left(Temp, P2 - 1) & Replacevalue2 & _
Mid(Temp, P2 + Len(WhatToReplace2))
P2 = InStr(P2 + Len(Replacevalue2), Temp, WhatToReplace2, 1)
Loop
replace = Temp
End Function

//the above doesn't really work correctly. can you please suggest an
alternative.

Thank you

 

[Wayne Morgan]

What version of Access are you using? Access 2000 and newer already have a
function called Replace that will do this. From what I remember, Access 2000
had a problem with the function being used in queries. To work around this,
you had to create your own function (with a different name, such as
MyReplace) and call that function from the query. You could use the built-in
VBA Replace function in your function, so the coding was real short and
simple.

 

[Wayne Morgan]

Just going by your second function, one problem I see is that you are
looping as long as P1 AND P2 are greater than 0. What if there are more
copies of P1 than there are of P2? You need to loop using Or instead of And.
You may actually find it simpler to just do this as two loops. One for the
first replacement until it is done and one for the second replacement. Of
course, if you're going to do that, you could just call the first function
twice, once with each replacement.

The problem with trying to do them together is that if the strings "overlap"
they may cut each other out.

Example:
Input String -bcdbcdbcd
1st Replace - bc with xx
2nd Replace - cd with zz

After one pass, you would have xxdbzzbcd. As you can see, there is one bc
left to be replaced on the next loop. Once it is replaced, there will be no
cd. Had all of the bc's been replaced first, there would never have been a
cd to be replaced. So, if you did the first replace to its conclusion, then
did the second replace, the result would be xxdxxdxxd. Which do you prefer?