SQL Update Query Syntax

[Phred]

Access 2007, not compiled, not split, Windows 64 bit Pro. Skill level moderate. One user stand alone, not networked.

Having a problem with syntax on an update query. Debug.Print returns all the correct information. The watches on the variables all come up correct. I'm not sure what's wrong.

Only one record exists in the table to be updated. There will never be more than one record. This is an override fix for a client. The only error I get is 3144 Syntax error.

Private Sub CmbPickSiteCount_AfterUpdate()

Dim stSQL As String
Dim intSiteCatID As Integer
Dim stSiteName As String
Dim intCount As Integer
Dim intSiteID As Integer
Dim intCustID As Integer
Dim stCustName As String
Dim stCatNM As String

intSiteCatID = Forms!frm_main!Frm_AddDateToCategoryParent!CmbPickSiteCount.Column(0)

stSiteName = Forms!frm_main!Frm_AddDateToCategoryParent!CmbPickSiteCount.Column(1)

intCount = Forms!frm_main!Frm_AddDateToCategoryParent!CmbPickSiteCount.Column(2)

intSiteID = Forms!frm_main!Frm_AddDateToCategoryParent!CmbPickSiteCount.Column(3)

intCustID = Forms!frm_main!Frm_AddDateToCategoryParent!CmbPickSiteCount.Column(4)

stCustName = Forms!frm_main!Frm_AddDateToCategoryParent!CmbPickSiteCount.Column(5)

stCatNM = Forms!frm_main!Frm_AddDateToCategoryParent!CmbPickSiteCount.Column(6)

stSQL = "UPDATE Tbl_ScheduledMeal6_Override" _
& " SET SiteName = '" & stSiteName & "'," _
& " SET DefaultCount = " & intCount & "," _
& " SET SiteID = " & intSiteID & "," _
& " SET CustID = " & intCustID & "," _
& " SET CustName = '" & stCustName & "'," _
& " WHERE CategoryName = '" & stCatNM & "';"

Debug.Print stSQL

DoCmd.RunSQL stSQL

End Sub

Results printed by Debug.Print:

UPDATE Tbl_ScheduledMeal6_Override SET SiteName = 'Kids Station', SET DefaultCount = 30, SET SiteID = 186, SET CustID = 113, SETUPDATE Tbl_ScheduledMeal6_Override SET SiteName = 'Kids Station', DefaultCount = 30, SiteID = 186, CustID = 113, CustName = 'Kids Station', WHERE CategoryName = 'LUNCH - PRESCHOOL';
CustName = 'Kids Station', WHERE CategoryName = 'LUNCH - PRESCHOOL'

Appreciate any help.

 

[pbaldy]

Correct syntax:

UPDATE TableName
SET Field1 = 123, Field2 = 'abc', Field3 = 123
WHERE Field = Whatever

 

[Phred]

Is this what you mean:

stSQL = "UPDATE Tbl_ScheduledMeal6_Override" _
& " SET SiteName = '" & stSiteName & "', DefaultCount = " & intCount & ", SiteID = " & intSiteID & ", CustID = " & intCustID & ", CustName = '" & stCustName & "'," _
& " WHERE CategoryName = '" & stCatNM & "';"

There is not enough detail in your example. I am still getting the error.

Should I be eliminating all of the quote marks and ampersands? You only show the single quotes areound text and none aroung numbers.

 

[pbaldy]

I showed the final results. The primary point was that your were repeating the SET keyword, and should not have. You also repeated the WHERE clause. Your concatenation of values looks like it was okay. What is the results of the Debug.Print now?

 

[pbaldy]

Oh, and you have a stray comma before the WHERE clause.

 

[Phred]

It was the comma at the end of the values. It just worked fine. Thanks for the help.

Phred

 

[pbaldy]

Happy to help.