loop for replace function

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

Access 2000 and later have a Replace() function.
Here's one that works like that:

Function Replace(strExpr As String, strFind As String, strReplace As String,
_
Optional lngStart As Long = 1) As String
Dim strOut As String
Dim lngLenExpr As Long
Dim lngLenFind As Long
Dim lng As Long

lngLenExpr = Len(strExpr)
lngLenFind = Len(strFind)

If (lngLenExpr 0) And (lngLenFind 0) And (lngLenExpr = lngStart)
Then
lng = lngStart
If lng 1 Then
strOut = Left$(strExpr, lng - 1)
End If
Do While lng = lngLenExpr
If Mid(strExpr, lng, lngLenFind) = strFind Then
strOut = strOut & strReplace
lng = lng + lngLenFind
Else
strOut = strOut & Mid(strExpr, lng, 1)
lng = lng + 1
End If
Loop
Replace = strOut
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"luzippu" wrote in message
...
//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

This is how I do it using the built-in Replace function in Access 2000 and
later. I believe it should work for your custom function too. Just pass the
result of one call to the Replace function as an argument to another call to
the same function ...

? replace(replace("some text","o","*"),"e","#")
s*m# t#xt

--
Brendan Reynolds (MVP)

"luzippu" wrote in message
...
//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