How to replace all items of a Column that are Like OR Equal to "Funny" in table??

[nicebasic]

I'm looking for a simple Query to replace the values of all items in a column of a table with another value.

1. We have an MS Access database with this name: "MyDatabase.MDB".
2. Our mentioned database has many tables. But, let's choose "tblDemo" here.
3. Our selected table (tblDemo) has many columns. But, let's choose "colName" here.

Now, I would like to change the value of all the items in "colName" whose value is Like or Equal to "Funny" from its current value to "Nervous". What should I do?

And, another thing is about case-sensitivity. Does it matter here or not?

Finally, does it make any difference for replacing numerical values instead of string variables in this case?

Thank you very much.

[Rabbit]

So you want an update query?

UPDATE tableName
SET columnName = "Replace With Text"
WHERE columnName Like "*Search Text*"

By default, it is not case sensitive.
There would be no difference between strings and numbers except for numbers you don't need the quotes.

[nicebasic]

Thank you for your prompt and excellent reply.

Can we know how many items have been replaced and changed with this query?

For example, if it finds 25 instances and replaces them with the "Replace String", is it possible to know how many fields have been replaced?

Another question, if you don't mind:

If we mean to have exact Search and Replace, is it enough to change Line 3 of your Code to this?

WHERE columnName = "Search Text"

Thank you very much again.

[Rabbit]

If this was set up as an update query in Access, it will say that # of records will be updated, do you wish to continue. That is, if you haven't turned off the warnings. They are on by default.

However, if you are doing this outside of Access, what you could do is use a Count first before you do an update.

SELECT Count(*) AS countOfChanges
FROM tableName 
WHERE columnName Like "*Search Text*" 

Yes, if you need an exact search and replace, you could use that.

[nicebasic]

Thank you again.

I did what you mentioned in Post #4 to COUNT the number of Replaced Records. But, I failed.

The code I have written based on your solution is as follows:

SELECT Count(*) AS countOfChanges
FROM tblContacts
WHERE GroupName Like 'Funny'
UPDATE tblContacts
SET GroupName = 'Nervous'
WHERE GroupName Like 'Funny'
'
'
MsgBox countOfChanges

I have attached a snapshot of the Error Dialog Box in VB.

And, this is the code I use to connect to the database and Execute Your Query:

Set Cn = New ADODB.Connection
Cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & AppPath & "DataB.MDB"
'
Rs.Open "UPDATE tblContacts SET GroupName = '" & txtReplace.Text & "' WHERE GroupName Like '" & txtSearch.Text & "'", Cn, adOpenKeyset, adLockOptimistic

Can I use something else instead of Rs.Open here? Maybe something else to use with Rs.(Method)?

Sorry if I ask you many questions.

[Rabbit]

You don't combine the two SQL queries. They have to be run separately.

You only have to run the select query if you're not using Access, if you're doing this in Access, then by default it will tell you how many records are being updated.

For the update query, you can use the cn.Execute method. But if you're in Access, you can just use the DoCmd.RunSQL command.

[nicebasic]

Thank you for your time and patience.

With your help, I could fix the problem.

Here's the final code I've written using your solution:

This is the code for General Declarations part in a VB Project:

Dim Cn As ADODB.Connection  ' for opening connection with database
Dim Rs As New ADODB.Recordset 'for opening recordset

1. Put a TextBox on your Form. Name it as "txtSearch".
2. Put a TextBox on your Form. Name it as "txtReplace".
3. Put a Command Button on your Form. Name it as "cmdRun" and add the following code to it:

Set Cn = New ADODB.Connection
Cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & AppPath & "DataB.MDB"
'
Set Rs = Cn.Execute("SELECT Count(*) As countOfChanges FROM tblContacts WHERE GroupName Like '" & txtSearch.Text & "'")
MsgBox CLng(Rs("countOfChanges"))
'
Cn.Execute "UPDATE tblContacts SET GroupName = '" & txtReplace.Text & "' WHERE GroupName Like '" & txtSearch.Text & "'"
'
Rs.Close
Cn.Close
Set Rs = Nothing
Set Cn = Nothing

I would like to thank "Rabbit" again for his contribution to this post.

[Rabbit]

Looks good. As for your MsgBox, you could do something along the lines of

If MsgBox(CLng(Rs("countOfChanges")) & " records will be updated. Do you wish to continue?", vbOkCancel, "Continue With Update") = vbOk Then
     Cn.Execute "UPDATE tblContacts SET GroupName = '" & txtReplace.Text & "' WHERE GroupName Like '" & txtSearch.Text & "'"
End If

[nicebasic]

Thank you, Rabbit.
This is a great suggestion. I added it to the Project.
Best wishes for you.