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

Delete Word in Field

P: 37
is there a way to delete just one word in a column in a database. right now i'm using replace with ''. but it's not the greatest option

** Edit **
This post moved from Sql insert with inner join.
Jul 16 '10 #1

✓ answered by NeoPa

Try some code like this :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE [MyTable] " & _
  2.          "SET [list]=IIf([list]=Forms!MyForm.txtWord," & _
  3.                         "Null," & _
  4.                         "Mid(Replace(',' & [Field1] & ','," & _
  5.                             "',' & Forms!MyForm.txtWord & ','," & _
  6.                             "','),2," & _
  7.                             "Len(Replace(',' & [Field1] & ','," & _
  8.                                 "',' & Forms!MyForm.txtWord & ','," & _
  9.                                 "','))-2)) " & _
  10.          "WHERE [list] Like '*' & Forms!MyForm.txtWord & '*'"
  11. Call CurrentDB.Execute(strSQL)
The SQL should end up something like :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [MyTable]
  2. SET [list]=IIf([list]=Forms!MyForm.txtWord,
  3.                Null,
  4.                Mid(Replace(',' & [Field1] & ',',
  5.                            ',' & Forms!MyForm.txtWord & ',',
  6.                            ','),2,Len(Replace(',' & [Field1] & ',',
  7.                                               ',' & Forms!MyForm.txtWord & ',',
  8.                                               ','))-2))
  9. WHERE [list] Like '*' & Forms!MyForm.txtWord & '*'
Did I mention it may be a little more complicated :D

Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,768
Do you mean to remove every occurrence of the word from within text in a field, or do you mean delete each entry which equals that word?

By delete, do you mean just delete the contents of the field, or do you mean delete each record that matches the criteria?
Jul 16 '10 #2

P: 37
this column is a list of keywords. each keyword connected to a specific product is unique. whatever is between two commas in this column will not be found elsewhere in the column. I want to delete the word(s) the user selected - so it would only be once. for example my column is ,hi,bye,how are you,fine,
The user chooses to delete ,bye, - that word will only show up once. i want the column to say ,hi,how are you,fine.
Jul 16 '10 #3

NeoPa
Expert Mod 15k+
P: 31,768
I can work with that. Much better.

What you have found, to use Replace, is about the best approach you could have come up with. I'm not quite sure if this is on a form or in an UPDATE query. I would guess the latter from your question, but confirmation (or otherwise could still help).

If we were working on a form, so one record at a time and the current record shown on the form, then when a word was selected (or entered) then code to remove it from the relevant control would be :
Expand|Select|Wrap|Line Numbers
  1. Dim strWord As String      'Word to remove
  2. Dim strList as String
  3. Dim intPos As Integer
  4. ...
  5. strList = "," & Me.txtList & ","
  6. strWord = "," & strWord & ","
  7. intPos = InStr(1, strList, strWord)
  8. strList = Replace(strList, strWord, ",")
  9. Me.txtList = Mid(strList, 2, Len(strList) - 2)
This is to ensure words like Bossanova never get replaced by anova when you're trying to get rid of all Bosses (That's a thought).

To do this as reliably in an UPDATE query (SQL) would be much harder. Let us know if it's what you need though.
Jul 16 '10 #4

P: 37
@NeoPa
What it looks like to me, is that you are deleting something from an array. I am trying to pass a parameter of what I need deleted into a sql query so it deletes from the database, thereby being deleted from my array.
Jul 16 '10 #5

NeoPa
Expert Mod 15k+
P: 31,768
That's absolutely right. That is what I was offering. Your post also answers my question (somewhat indirectly but good enough) so I now realise the SQL version will be required. This will certainly be a bit more clumsy as SQL doesn't handle work variables.

Let me see if I can come up with something for you over the weekend.

In the meantime, do you understand why it is necessary to do the searching and replacing with the separator characters surrounding the word you're working on?
Jul 16 '10 #6

P: 37
I do understand what you were doing, but since I am passing it in to sql as a parameter, it has to find exactly what it sends in ',hi,' it wouldn't be able to erase ,hit, because it has to be exactly the same. Thanx so much for putting in so much effort for me!
Jul 16 '10 #7

NeoPa
Expert Mod 15k+
P: 31,768
No worries. It's sort of fun.

I'm running very close to home-time now though so I'll look at it later. I have a pretty busy weekend but I should find time tonight if all goes well.
Jul 16 '10 #8

P: 37
Thank you. I appreciate it.
Jul 16 '10 #9

NeoPa
Expert Mod 15k+
P: 31,768
Try some code like this :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "UPDATE [MyTable] " & _
  2.          "SET [list]=IIf([list]=Forms!MyForm.txtWord," & _
  3.                         "Null," & _
  4.                         "Mid(Replace(',' & [Field1] & ','," & _
  5.                             "',' & Forms!MyForm.txtWord & ','," & _
  6.                             "','),2," & _
  7.                             "Len(Replace(',' & [Field1] & ','," & _
  8.                                 "',' & Forms!MyForm.txtWord & ','," & _
  9.                                 "','))-2)) " & _
  10.          "WHERE [list] Like '*' & Forms!MyForm.txtWord & '*'"
  11. Call CurrentDB.Execute(strSQL)
The SQL should end up something like :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [MyTable]
  2. SET [list]=IIf([list]=Forms!MyForm.txtWord,
  3.                Null,
  4.                Mid(Replace(',' & [Field1] & ',',
  5.                            ',' & Forms!MyForm.txtWord & ',',
  6.                            ','),2,Len(Replace(',' & [Field1] & ',',
  7.                                               ',' & Forms!MyForm.txtWord & ',',
  8.                                               ','))-2))
  9. WHERE [list] Like '*' & Forms!MyForm.txtWord & '*'
Did I mention it may be a little more complicated :D
Jul 16 '10 #10

P: 37
I've been able to do something similar to this and it works fine. I was just wondering if there was a way to use the word delete instead of replacing. thanx for all the help though
Jul 19 '10 #11

NeoPa
Expert Mod 15k+
P: 31,768
readbanana: I've been able to do something similar to this and it works fine. I was just wondering if there was a way to use the word delete instead of replacing.
No. There is no such function available to you. There would be little requirement for it when the Replace() function already handles it quite naturally. An analogy would be having a PC and also wanting a calculator. A PC may not be called a calculator, but it can manage the job perfectly well for all that.
Jul 19 '10 #12

P: 37
Ok. Thanx. I just wanted to make sure that I was doing it properly and not just working around my lack of knowledge.
Jul 19 '10 #13

Post your reply

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