CODEWITHSUNDEEP
sqlms-accessvbams-access-2013
Marchese Il Chihuahua
Marchese Il Chihuahua

Reputation: 1139

Update query with SQL

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

Upvotes: 0

Views: 170

Answers (3)

Bender
Bender

Reputation: 715

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

Upvotes: 2

HansUp
HansUp

Reputation: 97131

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.

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

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

Upvotes: 1

Related Questions