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

[osmosisgg]

Good Morning,

I need assistance with finding and deleting a string found in field[EM] from another field[Px] within the same table. I've tried in SQL and returned #error on fields with no data and the strings from field[EM] were still present in [Px].
One example of Tried & Failed in SQL:

SELECT SCAN.ID, Replace([px],"*[em]*","") AS Test FROM SCAN;

I did find a previous post with DropDupeWords for VBA, which works great when found in the same field. Is there a way to tweak it for use in my scenario- from one field to another?

You're help is requested!
-osmosisgg

[jforbes]

I think it's not working for you because there are a few things wrong with your SQL Statement.
There's a typo around [em].
Your using a Select statement instead of an Update Statement. Maybe your not trying to update the data, but without an Update Statement, you are just returning results.

This is a shot at the correct syntax, but it's just a best guess:

UPDATE SCAN SET [px]=Replace([px],"*" & [em] & "*","") FROM SCAN

You might want to try this also, so that only the records that need updated will get updated:

UPDATE SCAN SET [px]=Replace([px],"*" & [em] & "*","") FROM SCAN WHERE [px]<>Replace([px],"*" & [em] & "*","")

[osmosisgg]

When I ran them, "Syntax error (missing operator) in query expression 'Replace([px], "*" & [em] & "*","") from SCAN.

[PhilOfWalton]

It's a pure guess, but try

Replace(px.Text, "*" & em.Text & "*","") from SCAN
 

Phil

[osmosisgg]

I used this and did not receive any errors, but the string in [EM] is still in the [px] field.

UPDATE SCAN SET SCAN.px = Replace([px],"*" & [em] & "*",""); 

When I tried the below, no errors and no changes to the [px] field.

UPDATE SCAN SET SCAN.px = Replace([px],"*" & [em] & "*","")
WHERE px = Replace([px],"*" & [em] & "*",""); 

Anything else to try?

[NeoPa]

Try :

UPDATE [SCAN]
SET    [PX]=Replace([PX],[EM],'')
WHERE  ([PX] Is Not Null)
  AND  ([EM] Is Not Null)

[jforbes]

Nice, I don't know why I kept the * in there. They just messed it up.

[osmosisgg]

The query updates records with no errors, but no changes to [Px].

[osmosisgg]

Just looking over anything I missed. Not sure where to go with this, but replace [px] with instr[em]...?

[NeoPa]

In that case, and after having set up a table and query to test for myself, I can only ask what you're doing wrong - or what is different from what you've told us.

My results are :

It reported that two records were updated (as expected) and you'll notice that only the last record has been changed (correctly as expected).

[osmosisgg]

See line 304 or 317 for examples. These fields are text formatted due to some of the data contains actual text. They may be found in any order and are either in the format xxxxx-x or xxxxx-xx-x or xxxxx-xx-xx and occasionally as xxxxx-xx-xx-x.
I just noticed the EM column was not wide enough, but 304 actually has 99213-25-1 at the end. What should end up in [px] is 90471-1 90715-1

And now that I looked at that line, I just noticed further down that the dropdupewords vba left code Q0091 on line 317. On this line, [px] should only contain Q0091-1 once. In [EM], again, not wide enough, but has 99202-25-1 at the end.

Edited:
Line 304
EM: 99396-1, 99213-25-1
Px: 99213-25-1 90471-1 90715-1 , 99396-1 90471-1 90715-1
SHOULD be: PX: 90471-1 90715-1

Line 317
EM: 99386-1, 99202-25-1
Px: 99202-25-1 Q0091-1 , 99386-1 Q0091-1
SHOULD be: Px: Q0091-1

[NeoPa]

I'm sorry to say that you haven't managed to upload your JPEG file. That makes life a little difficult.

OTOH it seems clearer now that what you asked for is somewhat different from what you actually wanted. If I guess correctly you're actually wanting the field [EM] to be treated, not as a single string, but a list of strings. Each of those strings in the list should be removed from the field [Px] where they are found. Nowhere do you specify that in your question. You don't even allude to it and allow us to guess what you mean by explaining that any space in [EM] should be treated as a delimiter between the strings within [EM].

If I'm correct in my interpretation, and it would certainly explain why you might believe the posted SQL code didn't exactly fit your question, then what you're after is a lot more complicated. So much so that I doubt there's a way using standard available functions to achieve this within SQL. Almost anything's possible using VBA, and that includes writing a VBA function to use from SQL, but SQL alone, with all available functions, can't do it if my understanding of what's available is correct.

[osmosisgg]

Yesterday was a late night, but after sleeping and having some good coffee, I realized that I actually did not run the dropdupewords function and that is one reason I saw the dupe strings in [px]. I have now inserted that into my run.
Hopefully the JPEG file is attached this time.
It has been quite a while since I have done something with Access and never had much training...google was it. That is why I came to the folks who know best. It also helps me learn how to ask the "right" questions. :)

So, then is the correct question- if [px] contains a section of string found in [EM] then delete the sections of string from the [px]?
If the section of string is in the middle of [px], then it ([EM] string) should have a space before and after.
If the section of string is left of the [px], then a space would be after that ([EM] string).
If the section of string is right of the [px], then a space would be before that ([EM} string).

[PhilOfWalton]

At long last we know what you want.

I appreciate your struggle to send the image, but if you could send a portion of the data as an access table of Excel file with an additional column to show exactly the required output, it would make life much easier to play around with your data.

What you want is not too difficult, but will require fiddling around with.

Phil.

[osmosisgg]

Thank you for sticking with me on this. Phew! Glad I made some sense finally! The Px AFTER column should be the results.
-osmosisgg

[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 "DoScanThing" 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...google 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 ;-)