Delete Word in Field

[readbanana]

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.

[NeoPa]

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?

[readbanana]

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.

[NeoPa]

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 :

Dim strWord As String      'Word to remove
Dim strList as String
Dim intPos As Integer
...
strList = "," & Me.txtList & ","
strWord = "," & strWord & ","
intPos = InStr(1, strList, strWord)
strList = Replace(strList, strWord, ",")
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.

[readbanana]

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 :

Dim strWord As String      'Word to remove
Dim strList as String
Dim intPos As Integer
...
strList = "," & Me.txtList & ","
strWord = "," & strWord & ","
intPos = InStr(1, strList, strWord)
strList = Replace(strList, strWord, ",")
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.

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.

[NeoPa]

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?

[readbanana]

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!

[NeoPa]

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.

[readbanana]

Thank you. I appreciate it.

[NeoPa]

Try some code like this :

strSQL = "UPDATE [MyTable] " & _
         "SET [NoParse][List][/NoParse]=IIf([NoParse][List][/NoParse]=Forms!MyForm.txtWord," & _
                        "Null," & _
                        "Mid(Replace(',' & [Field1] & ','," & _
                            "',' & Forms!MyForm.txtWord & ','," & _
                            "','),2," & _
                            "Len(Replace(',' & [Field1] & ','," & _
                                "',' & Forms!MyForm.txtWord & ','," & _
                                "','))-2)) " & _
         "WHERE [NoParse][List][/NoParse] Like '*' & Forms!MyForm.txtWord & '*'"
Call CurrentDB.Execute(strSQL)

The SQL should end up something like :

UPDATE [MyTable]
SET [NoParse][List][/NoParse]=IIf([NoParse][List][/NoParse]=Forms!MyForm.txtWord,
               Null,
               Mid(Replace(',' & [Field1] & ',',
                           ',' & Forms!MyForm.txtWord & ',',
                           ','),2,Len(Replace(',' & [Field1] & ',',
                                              ',' & Forms!MyForm.txtWord & ',',
                                              ','))-2))
WHERE [NoParse][List][/NoParse] Like '*' & Forms!MyForm.txtWord & '*'

Did I mention it may be a little more complicated :D

[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. thanx for all the help though

[NeoPa]

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.

[readbanana]

Ok. Thanx. I just wanted to make sure that I was doing it properly and not just working around my lack of knowledge.