If field[Px] contains string[EM], delete the string[EM] from the field[Px]

[PhilOfWalton]

Great fun

Using your table, I have created an additional field called PhilsPx to compare results.

I also have a form with a command button called "DoScanThin g" for testing.

This is the code

Private Sub DoScanThing_Click()

    Dim MyDb As Database
    Dim ScanSet As Recordset
    Dim StrSQL As String
    Dim StrSearchFor() As String
    Dim StrSearchIn() As String
    Dim i As Integer, j As Integer, k As Integer
    
    StrSQL = "SELECT Scan.* FROM SCAN ORDER BY ID"
    
    Set MyDb = CurrentDb
    Set ScanSet = MyDb.OpenRecordset(StrSQL)
    
    With ScanSet
        Do Until .EOF
            .Edit
            !PhilsPX = Null
            .Update
            .MoveNext
        Loop
        .MoveFirst
        Do Until .EOF
            Debug.Print !ID; "  ";
            'If !ID = 304 Then Stop
            StrSearchFor = Split(!Em, ",")
            For i = 0 To UBound(StrSearchFor)
                StrSearchFor(i) = Trim(StrSearchFor(i))
                Debug.Print StrSearchFor(i); "   ";
            Next i
            
            Debug.Print "PX: ";
            StrSearchIn = Split(!Pxbefore, " ,")
            For j = 0 To UBound(StrSearchIn)
                StrSearchIn(j) = Trim(StrSearchIn(j))
                Debug.Print StrSearchIn(j); "   ";
            Next j
            
            'Look for match
            .Edit
            For j = 0 To UBound(StrSearchIn)
                For i = 0 To UBound(StrSearchFor)
                    k = InStr(StrSearchIn(j), StrSearchFor(i))
                    If k > 0 Then                   ' Match found
                        'Stop
                        StrSearchIn(j) = Replace(StrSearchIn(j), StrSearchFor(i), "")
                        !PhilsPX = !PhilsPX & Trim(StrSearchIn(j))
                    End If
                Next i
                
            Next j
            .Update
            .MoveNext
            Debug.Print
        Loop
        .Close
        Set ScanSet = Nothing
    End With
    
    ' Now go through again and add the unchanged values
    StrSQL = "SELECT Scan.* FROM SCAN WHERE IsNull(PhilsPX) ORDER BY ID"
    
    Set ScanSet = MyDb.OpenRecordset(StrSQL)
    
    With ScanSet
        Do Until .EOF
            .Edit
            !PhilsPX = !Pxbefore
            .Update
            .MoveNext
        Loop
        .Close
        Set ScanSet = Nothing
    End With
    
End Sub

It is not particularly elegant, but seems to produce the correct results, and you need to remove the Debug statements.

Phil

[osmosisgg]

I added [PhilsPX] to the Scan table. First try I received runtime error '3265' on

StrSearchIn = Split(!Pxbefore, " ,")

So I renamed the my field from Px to [Pxbefore]. It worked like a charm. Now of course, my luck- here it goes. I thought I would use the actual Scan table, changed within the code you created to !px and get runtime error 94 on

StrSearchIn = Split(!PX, " ,")

hmmmm...maybe I should just leave it as pxbefore?

[PhilOfWalton]

Please when quoting error numbers, dive the error as well as the number. You have that information: I have to look it up.

It could be caused by in some cases your Px field is null and in other cases it's an empty space ""

So when the program stops and gives the error, check the value of ID and have a look at that line in the SCAN table.

If it is a problem with a Null value you need to change the code t

     If not IsBull(!Em) then
            StrSearchFor = Split(!Em, ",")
            For i = 0 To UBound(StrSearchFor)
                StrSearchFor(i) = Trim(StrSearchFor(i))
                Debug.Print StrSearchFor(i); "   ";
            Next i
      End If

and similarly check for a null on !PxBefore

I f you want to change PxBefore back to Px, it must be done in the SCAN table and on every occurrence in the code.

Phil

[osmosisgg]

You are awesome!!
I did keep Pxbefore since it makes most sense for my sanity. I will just do an update query to the final table.

Again, you are awesome and thank you very, very much!

[PhilOfWalton]

Well we got there in the end.
Happy to help.

Phil

[NeoPa]

OsmosisGG:
It has been quite a while since I have done something with Access and never had much training...goog le was it. That is why I came to the folks who know best. It also helps me learn how to ask the "right" questions. :)

Good for you. Showing a very positive attitude, and getting the questions right is a very important step (For you as well as us of course).

I see that Phil has helped you to a solution now. Excellent. Not nearly as elegant as the pure SQL, but for a much more complicated question.

PS. Notice in Phil's code that he Split()s on the comma (,) then Split()s the result. This ensures the code still works if the data contains ", " as well as " ,", or even just "," on its own. Good defensive programming ;-)