VBA+REPLACE()+WHILE Loop

[murthyspd]

Hi,
I have pulled the Event_log field in a recordset. That field have a long string. There is a substring "Department=664573". This string ends with a new line character. I want to replace 664645 with a value "@@@@@@" in all the rows. To do this, I am finding the string "Department=" using INSTR() function. Assign the position returned by INSTR() TO a variable intPosition. Then add the length of "Department=" to intPosition. From that position, till I reach the new line character, I want to replace every character in "664573" with "@@@@@@". For some reason, Replace is not working with While loop.

For example

Event_log field in Access has value :

"1311614974Email Submit ProgramEmployee Badge=298379
Employee Name=WAYNE16_WILLIAMS
GL US CORP USD RESPONSIBILITIES=GL US_CORP_USD USER
Domain=AMERICAS
Department=664573
NT Login ID=WAYNE_W



My REPLACE function looks like this
rsREPLACE.Open "SELECT * FROM 1_tblWOW", con, adOpenKeyset, adLockOptimistic

While Not rsREPLACE.EOF

rsREPLACE!EVENT_LOG = Replace(rsREPLACE!EVENT_LOG, "Approval by", "Approved by")

intPosition = InStr(1, rsREPLACE!EVENT_LOG, "Department=")
If intPosition <> 0 Then
intPosition = intPosition + Len("Department=")
While Mid(rsREPLACE!EVENT_LOG, intPosition, 1) <> Chr(13)
rsREPLACE!EVENT_LOG = Replace(rsREPLACE!EVENT_LOG, Mid(rsREPLACE!EVENT_LOG, intPosition, 1), "@", intPosition, 1)
intPosition = intPosition + 1
Wend
End If
rsREPLACE.MoveNext
Wend


I am seeing the output as
#64573
NT Login ID=WAYNE_W

Can somebody help ?
I would like to see the output as
1311614974Email Submit ProgramEmployee Badge=298379
Employee Name=WAYNE16_WILLIAMS
GL US CORP USD RESPONSIBILITIES=GL US_CORP_USD USER
Domain=AMERICAS
Department=@@@@@@
NT Login ID=WAYNE_W

Thanks
Murthy

 

[SydneyGeek]

Try this instead...

Function AnonDept(r As String) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "Department=\d\d\d\d\d\d"
        .multiline = True
        AnonDept = .Replace(r, "Department=@@@@@@")
    End With
End Function

Function CleanData()
    Dim rst As DAO.Recordset
    Dim strText As String
    
    Set rst = CurrentDb.TableDefs("tblSomeTable").OpenRecordset
    
    With rst
        .MoveFirst
        Do Until .EOF
            .Edit
            strText = !sometext
            strText = AnonDept(strText)
            !sometext = strText
            .Update
            .MoveNext
        Loop
    End With
    
    rst.Close
    Set rst = Nothing
End Function

Change the recordset and field names to suit

Denis