Update query with SQL

Question

could someone please have a quick look at the query i am writing below. A synopsis of what this query is suppose to be doing is to put the two characters "xx" in the field "Query04priorityselect" for all records where priority = "high". It seems that all is working other than the second to last line. The error message I get is "syntax error, missing operator" upon executing the query. Thanks,

Private Sub Opzione61_GotFocus()

' identifies table to be updated
Dim Recordset
    Set Recordset = CurrentDb.OpenRecordset("tblsearchengine01")

' puts a 1 value in the field Query04PrioritySelect for all records
Dim ClearPriority
    ClearPriority = "UPDATE tblsearchengine01 SET Query04priorityselect=1"

' Run the command. / perform the update
    CurrentDb.Execute ClearPriority

Dim HighPriority
    HighPriority = "UPDATE tblsearchengine01 SET Query04priorityselect = ""xx"" & WHERE Priority<>high"
    CurrentDb.Execute HighPriority

End Sub

"UPDATE tblsearchengine01 SET Query04priorityselect = 'xx' WHERE Priority <> 'high' " — ray
– ray, Commented Apr 21, 2014 at 6:25

HansUp · Accepted Answer · 2014-04-21 06:28:13Z

2

I think you need to quote the word high in the WHERE clause. Single quotes are fine in Access SQL statements.

HighPriority = "UPDATE tblsearchengine01" & vbCrLf & _
    "SET Query04priorityselect = 'xx'  WHERE Priority <> 'high'"
Debug.Print HighPriority
CurrentDb.Execute HighPriority

If the query still throws an error, go to the Immediate window and copy the statement text which was output from Debug.Print. You can then create a new query in the Access query designer, switch it to SQL View, paste in the copied text and test that statement. Hopefully you can figure out how to fix the error. If not, copy the statement text and include it in your question.

answered Apr 21, 2014 at 6:28

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bender · Accepted Answer · 2014-04-21 06:31:44Z

2

Dim HighPriority
HighPriority = "UPDATE tblsearchengine01 SET Query04priorityselect ='xx'  WHERE Priority<>high "
CurrentDb.Execute HighPriority

edited Apr 21, 2014 at 6:31

answered Apr 21, 2014 at 6:25

Bender

71511 silver badges26 bronze badges

Comments

Vignesh Kumar A · Accepted Answer · 2014-04-21 06:27:00Z

1

You don't need & between WHERE and Query04priorityselect = 'xx' Try this

Dim HighPriority
    HighPriority = "UPDATE tblsearchengine01 SET Query04priorityselect = 'xx'  WHERE Priority<>'high'"
    CurrentDb.Execute HighPriority

answered Apr 21, 2014 at 6:27

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Collectives™ on Stack Overflow

Update query with SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related