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 :
[IMGNOTHUMB]https://bytes.com/attachment.php? attachmentid=91 62&d=149686888 0[/IMGNOTHUMB]
[IMGNOTHUMB]https://bytes.com/attachment.php? attachmentid=91 63&d=149686888 0[/IMGNOTHUMB]
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...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. :)

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