sql update statement syntax error

[osupratt]

i am new to this and have searched through other threads and have tried numerous attempts which have all failed miserably. i have gone through the baldy tutorial and i just am not getting it. ok, i have a bunch of update queries that run fine and do the job. i have read (and may be wrong on this) is the way to go though is to do a piece of VBA with a sql statement instead. so in this learning process i would like to understand the upside to doing things the sql statement way. anyhow i am trying to update a column in a table from a command button on a form. here is what i have and it keeps giving me a compile error that it is expecting the end of the statement (bold font):

Private Sub Command22_Click()
Dim dbCMC As DAO.Database
Dim strSQL As String
Set dbCMC = CurrentDb
strSQL = "UPDATE dbo_TestFTHeader " & _
"SET dbo_TestFTHeader.Customer = " & Me.Customer & " & _
"WHERE dbo_TestFTHeader.Misc_Text_Field4 = " & Me.SOBatchID & "


Debug.Print strSQL
dbCMC.Execute strSQL, dbFailOnError

End Sub


obviously i could have other errors but i can't get past this first bridge i am at. any help would be greatly appreciated. signed STRUGGLING.

[JoeM]

I think you may be getting an error because you need to have a literal space within the string you are building between the two lines, or else the "WHERE" word is going to run into the end of your Customer variable, and make an phrase it doesn't understand. You should be able to see this if you view the SQL code you are building before trying to process it.

Also, if "Customer" and SOBatchID" are text fields, make sure to add text qualifiers surrounding it in your code. So maybe something like this:

strSQL = "UPDATE dbo_TestFTHeader " & _
"SET dbo_TestFTHeader.Customer = '" & Me.Customer & "' " & _
"WHERE dbo_TestFTHeader.Misc_Text_Field4 = '" & Me.SOBatchID & "'"

[ssanfu]

What Joe said.

But also, the error you are getting is because of an extra quote and extra ampersand that shouldn't be there

"SET dbo_TestFTHeader.Customer = " & Me.Customer & " & _
"WHERE dbo_TestFTHeader.Misc_Text_Field4 = " & Me.SOBatchID & "

I dislike the line continuation form (method?) to build the SQL because it is hard to find errors.
I build strings like this: (assuming that Me.Customer and Me.SOBatchID are numbers)

strSQL = "UPDATE dbo_TestFTHeader" 
strSQL = strSQL & " SET dbo_TestFTHeader.Customer = " & Me.Customer
strSQL = strSQL & " WHERE dbo_TestFTHeader.Misc_Text_Field4 = " & Me.SOBatchID
'  Debug.Print strSQL

Note the space before "SET" and "WHERE". I put the space at the beginning of the line instead of the end because a missing space is easier to see.

My $0.02

[Dal Jeanis]

Heh, heh.

Ssanfu - I use the concatenation and the space-at-the-end for the exact same reasons you use the other way.

The difference is when you have a MASSIVE SQL statement, you can use indentation on the SQL to make it more readable, and trading the far left seventeen characters (strSQL = strSQL & ) for three characters on the right (& _) means you don't lose many lines of code doing it.

[osupratt]

Thanks to both of you as I got it to work. A question now though. What if i want to have one command button on this form that runs multiple of these update statements. would i just copy for example the code that JoeM gave me and paste it underneath each other or how would i do this? Again thanks for your help.

[ssanfu]

Yes, that would do it.

Did you try it?

[osupratt]

yes i got it. thanks again.

[Dal Jeanis]

good job. PLease mark thread "solved". top of page, under "Thread Tools".