loop on module function

[luzippu]

//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

 

[Graham R Seach]

See the following page for examples:
http://www.pacificdb.com.au/MVP/Code/repl.htm

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia