Correct syntax for an UPDATE statement

Question

I'm struggling to draft a functional UPDATE statement and would welcome some help.

I'm working with Access 2010 and want a procedure that selects a record and, if the text meets a specified requirement i.e. contains a specified string, replaces that string with another.

The code that I've written is as follows:

        

Sub SortText2()

Dim Qry
Dim rds As Recordset
Dim SearchKW As String, Result As String, ReplaceKW As String, NewStr1 As String
Dim UpdateCommand As String
Dim StrLen As Long, Position As Long

SearchKW = InputBox("What is your Key Word that you want to replace?")
ReplaceKW = InputBox("What is your Replacement Key Word?")

Qry = "SELECT Table1.Field1 FROM Table1"

Set rds = CurrentDb.OpenRecordset(Qry)

Do Until rds.EOF

    Result = rds("Field1")

    UpdateCommand = "UPDATE Table1 SET Field1=Replace(Result, SearchKW, ReplaceKW)"
    CurrentDb.Execute (UpdateCommand)

rds.MoveNext
Loop

End Sub

Table 1 has one field other than its ID field. This is Field1. Field1 holds five records, each a string containing two or more words separated by commas. I know that the SELECT statement works but however I structure the UPDATE statement I get either parameter missing errors or syntax errors. Suggestions gratefully received

Answer 1

Hi,

There's really no need to loop through the records if you're using an UPDATE query. In other words, you don't need the Do Until rds.EOF line. In fact, you don't even need a recordset, so Set rds = CurrentDb.OpenRecordset(Qry) is not really necessary. You should be able to simply execute something like the following line:

strSQL = "UPDATE Table1 SET Field1=Replace(Field1,'" & SearchKW & "','" & ReplaceKW & "')"

Hope it helps...

Answer 2

There is no need to open a recordset and to loop through the records. Executing an Update SQL statement is sufficient, but you need to place the variables outside the quotes:

Sub SortText2()
    Dim SearchKW As String, ReplaceKW As String
    Dim UpdateCommand As String

    SearchKW = InputBox("What is your Key Word that you want to replace?")
    If SearchKW = "" Then
        MsgBox "You haven't specified a keyword to search for!", vbExclamation
        Exit Sub
    End If
    ReplaceKW = InputBox("What is your Replacement Key Word?")
    ' Optional
    If ReplaceKW = "" Then
        MsgBox "You haven't specified a replacement keyword!", vbExclamation
        Exit Sub
    End If

    UpdateCommand = "UPDATE Table1 SET Field1=Replace(Field1, " & Chr(34) & _
        SearchKW & Chr(34) & ", " & Chr(34) & ReplaceKW & Chr(34) & ")"
    ' If you want to inspect the command first:
    ' MsgBox UpdateCommand
    CurrentDb.Execute UpdateCommand, dbFailOnError
End Sub

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 3

Thank you for your suggested solution. This was very helpful, particularly the formatting.

Answer 4

Thank you for your helpful response. Your approach is really interesting. I also appreciate your advice about placing my variables outside the quotation marks.

Answer 5

Hi,

Glad to hear you got it sorted out. Hans and I were happy to assist. Good luck with your project.