MS ACCESS VBA Module remove portions of a string based on phrases in another table (1 Viewer)

[hyperdreamz]

Request help in creating a MS ACCESS VBA Module to remove portions of a string based on phrases in another table.

I am not a programmer but has a basic understanding of MS ACCESS & VBA.

Any help would be appreciated.

Table 1 with over a million records contains FIELD1 with large amounts running text(chats transcripts)


Table 2 contains a large number of restricted phrases in FIELD2 I wish to remove from the running text without leaving extra spaces


In order to generate OUTPUT table below

TABLE1
---------
FIELD1
This is normal text.
This is not normal text.
This maybe normal text. This normal is text.
This can be normal text.
This normal is text. This normal is text.

.

TABLE2
---------
FIELD2
is normal
is text

.

OUTPUT
---------
FIELD1
This text.
This is not normal text.
This maybe normal text. This normal.
This can be normal text.
This normal. This normal.

.

 

[theDBguy]

Hi. Have you tried using the Replace() function?

 

[June7]

Cross post https://stackoverflow.com/questions...tions-of-a-string-based-on-phrases-in-a-table

Loop through recordset of table2 and execute UPDATE with Replace() on table1 then run another UPDATE on table1 to Replace() double spaces with single space.

 

[arnelgp]

supposing the Output field is also in Table1.
create a Query that will update Output field:

UPDATE TABLE1 SET TABLE1.[OUTPUT] = apReplace([FIELD1],"TABLE2","FIELD2");

copy and paste the function in a Module.

Public Function apReplace(ByVal varTarget As Variant, strSourceTable As String, strSourceField As String) As Variant
    Dim strSought As String
    If IsNull(varTarget) Then
        Exit Function
    End If
    varTarget = varTarget & vbNullString
    With CurrentDb.OpenRecordset( _
            "select [" & strSourceField & "] from [" & strSourceTable & "];", _
            dbOpenDynaset)
            
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        While Not .EOF
            strSought = .Fields(0)
            With CreateObject("VBScript.RegExp")
                .Global = True
                .IgnoreCase = True
                .pattern = "\b" & strSought & "\b"
                varTarget = .Replace(varTarget, "")
            End With
            .MoveNext
        Wend
    End With
    apReplace = varTarget
End Function