By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

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

P: 51
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Jun 7 '17 #1

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1. Private Sub DoScanThing_Click()
  2.  
  3.     Dim MyDb As Database
  4.     Dim ScanSet As Recordset
  5.     Dim StrSQL As String
  6.     Dim StrSearchFor() As String
  7.     Dim StrSearchIn() As String
  8.     Dim i As Integer, j As Integer, k As Integer
  9.  
  10.     StrSQL = "SELECT Scan.* FROM SCAN ORDER BY ID"
  11.  
  12.     Set MyDb = CurrentDb
  13.     Set ScanSet = MyDb.OpenRecordset(StrSQL)
  14.  
  15.     With ScanSet
  16.         Do Until .EOF
  17.             .Edit
  18.             !PhilsPX = Null
  19.             .Update
  20.             .MoveNext
  21.         Loop
  22.         .MoveFirst
  23.         Do Until .EOF
  24.             Debug.Print !ID; "  ";
  25.             'If !ID = 304 Then Stop
  26.             StrSearchFor = Split(!Em, ",")
  27.             For i = 0 To UBound(StrSearchFor)
  28.                 StrSearchFor(i) = Trim(StrSearchFor(i))
  29.                 Debug.Print StrSearchFor(i); "   ";
  30.             Next i
  31.  
  32.             Debug.Print "PX: ";
  33.             StrSearchIn = Split(!Pxbefore, " ,")
  34.             For j = 0 To UBound(StrSearchIn)
  35.                 StrSearchIn(j) = Trim(StrSearchIn(j))
  36.                 Debug.Print StrSearchIn(j); "   ";
  37.             Next j
  38.  
  39.             'Look for match
  40.             .Edit
  41.             For j = 0 To UBound(StrSearchIn)
  42.                 For i = 0 To UBound(StrSearchFor)
  43.                     k = InStr(StrSearchIn(j), StrSearchFor(i))
  44.                     If k > 0 Then                   ' Match found
  45.                         'Stop
  46.                         StrSearchIn(j) = Replace(StrSearchIn(j), StrSearchFor(i), "")
  47.                         !PhilsPX = !PhilsPX & Trim(StrSearchIn(j))
  48.                     End If
  49.                 Next i
  50.  
  51.             Next j
  52.             .Update
  53.             .MoveNext
  54.             Debug.Print
  55.         Loop
  56.         .Close
  57.         Set ScanSet = Nothing
  58.     End With
  59.  
  60.     ' Now go through again and add the unchanged values
  61.     StrSQL = "SELECT Scan.* FROM SCAN WHERE IsNull(PhilsPX) ORDER BY ID"
  62.  
  63.     Set ScanSet = MyDb.OpenRecordset(StrSQL)
  64.  
  65.     With ScanSet
  66.         Do Until .EOF
  67.             .Edit
  68.             !PhilsPX = !Pxbefore
  69.             .Update
  70.             .MoveNext
  71.         Loop
  72.         .Close
  73.         Set ScanSet = Nothing
  74.     End With
  75.  
  76. End Sub
  77.  
It is not particularly elegant, but seems to produce the correct results, and you need to remove the Debug statements.

Phil

Share this Question
Share on Google+
20 Replies


jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. UPDATE SCAN SET [px]=Replace([px],"*" & [em] & "*","") FROM SCAN WHERE [px]<>Replace([px],"*" & [em] & "*","")
Jun 7 '17 #2

P: 51
When I ran them, "Syntax error (missing operator) in query expression 'Replace([px], "*" & [em] & "*","") from SCAN.
Jun 7 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
It's a pure guess, but try

Expand|Select|Wrap|Line Numbers
  1. Replace(px.Text, "*" & em.Text & "*","") from SCAN
  2.  
Phil
Jun 7 '17 #4

P: 51
I used this and did not receive any errors, but the string in [EM] is still in the [px] field.
Expand|Select|Wrap|Line Numbers
  1. UPDATE SCAN SET SCAN.px = Replace([px],"*" & [em] & "*",""); 
When I tried the below, no errors and no changes to the [px] field.
Expand|Select|Wrap|Line Numbers
  1. UPDATE SCAN SET SCAN.px = Replace([px],"*" & [em] & "*","")
  2. WHERE px = Replace([px],"*" & [em] & "*",""); 
Anything else to try?
Jun 7 '17 #5

NeoPa
Expert Mod 15k+
P: 31,492
Try :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [SCAN]
  2. SET    [PX]=Replace([PX],[EM],'')
  3. WHERE  ([PX] Is Not Null)
  4.   AND  ([EM] Is Not Null)
Jun 7 '17 #6

jforbes
Expert 100+
P: 1,107
Nice, I don't know why I kept the * in there. They just messed it up.
Jun 7 '17 #7

P: 51
The query updates records with no errors, but no changes to [Px].
Jun 7 '17 #8

P: 51
Just looking over anything I missed. Not sure where to go with this, but replace [px] with instr[em]...?
Jun 7 '17 #9

NeoPa
Expert Mod 15k+
P: 31,492
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).
Attached Images
File Type: jpg Before.JPG (17.9 KB, 79 views)
File Type: jpg After.JPG (17.1 KB, 90 views)
Jun 7 '17 #10

P: 51
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
Jun 7 '17 #11

NeoPa
Expert Mod 15k+
P: 31,492
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.
Jun 7 '17 #12

P: 51
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).

Attached Images
File Type: jpg S1.jpg (92.1 KB, 71 views)
Jun 8 '17 #13

PhilOfWalton
Expert 100+
P: 1,430
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.
Jun 8 '17 #14

P: 51
Thank you for sticking with me on this. Phew! Glad I made some sense finally! The Px AFTER column should be the results.
-osmosisgg
Attached Files
File Type: xlsx SCAN.xlsx (9.5 KB, 127 views)
Jun 8 '17 #15

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub DoScanThing_Click()
  2.  
  3.     Dim MyDb As Database
  4.     Dim ScanSet As Recordset
  5.     Dim StrSQL As String
  6.     Dim StrSearchFor() As String
  7.     Dim StrSearchIn() As String
  8.     Dim i As Integer, j As Integer, k As Integer
  9.  
  10.     StrSQL = "SELECT Scan.* FROM SCAN ORDER BY ID"
  11.  
  12.     Set MyDb = CurrentDb
  13.     Set ScanSet = MyDb.OpenRecordset(StrSQL)
  14.  
  15.     With ScanSet
  16.         Do Until .EOF
  17.             .Edit
  18.             !PhilsPX = Null
  19.             .Update
  20.             .MoveNext
  21.         Loop
  22.         .MoveFirst
  23.         Do Until .EOF
  24.             Debug.Print !ID; "  ";
  25.             'If !ID = 304 Then Stop
  26.             StrSearchFor = Split(!Em, ",")
  27.             For i = 0 To UBound(StrSearchFor)
  28.                 StrSearchFor(i) = Trim(StrSearchFor(i))
  29.                 Debug.Print StrSearchFor(i); "   ";
  30.             Next i
  31.  
  32.             Debug.Print "PX: ";
  33.             StrSearchIn = Split(!Pxbefore, " ,")
  34.             For j = 0 To UBound(StrSearchIn)
  35.                 StrSearchIn(j) = Trim(StrSearchIn(j))
  36.                 Debug.Print StrSearchIn(j); "   ";
  37.             Next j
  38.  
  39.             'Look for match
  40.             .Edit
  41.             For j = 0 To UBound(StrSearchIn)
  42.                 For i = 0 To UBound(StrSearchFor)
  43.                     k = InStr(StrSearchIn(j), StrSearchFor(i))
  44.                     If k > 0 Then                   ' Match found
  45.                         'Stop
  46.                         StrSearchIn(j) = Replace(StrSearchIn(j), StrSearchFor(i), "")
  47.                         !PhilsPX = !PhilsPX & Trim(StrSearchIn(j))
  48.                     End If
  49.                 Next i
  50.  
  51.             Next j
  52.             .Update
  53.             .MoveNext
  54.             Debug.Print
  55.         Loop
  56.         .Close
  57.         Set ScanSet = Nothing
  58.     End With
  59.  
  60.     ' Now go through again and add the unchanged values
  61.     StrSQL = "SELECT Scan.* FROM SCAN WHERE IsNull(PhilsPX) ORDER BY ID"
  62.  
  63.     Set ScanSet = MyDb.OpenRecordset(StrSQL)
  64.  
  65.     With ScanSet
  66.         Do Until .EOF
  67.             .Edit
  68.             !PhilsPX = !Pxbefore
  69.             .Update
  70.             .MoveNext
  71.         Loop
  72.         .Close
  73.         Set ScanSet = Nothing
  74.     End With
  75.  
  76. End Sub
  77.  
It is not particularly elegant, but seems to produce the correct results, and you need to remove the Debug statements.

Phil
Jun 8 '17 #16

P: 51
I added [PhilsPX] to the Scan table. First try I received runtime error '3265' on
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. StrSearchIn = Split(!PX, " ,")
hmmmm...maybe I should just leave it as pxbefore?
Jun 8 '17 #17

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1.      If not IsBull(!Em) then
  2.             StrSearchFor = Split(!Em, ",")
  3.             For i = 0 To UBound(StrSearchFor)
  4.                 StrSearchFor(i) = Trim(StrSearchFor(i))
  5.                 Debug.Print StrSearchFor(i); "   ";
  6.             Next i
  7.       End If
  8.  
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
Jun 8 '17 #18

P: 51
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!
Jun 8 '17 #19

PhilOfWalton
Expert 100+
P: 1,430
Well we got there in the end.
Happy to help.

Phil
Jun 8 '17 #20

NeoPa
Expert Mod 15k+
P: 31,492
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 ;-)
Jun 8 '17 #21

Post your reply

Sign in to post your reply or Sign up for a free account.