UPDATE syntax question

[syoo]

Hi,

I'm trying to update multiple fields using the UPDATE function but cannot get it to work.

strSQL = "UPDATE Estimates SET [Project Name]= '"
strSQL = strSQL & item.Subject
'strSQL = strSQL & "','"[Location]= '"
'strSQL = strSQL & item.Location
strSQL = strSQL & "'WHERE [EstimateID] = " & item.ID

conDB.Execute strSQL

The above command successfully updates the Project Name but when I take the comment off lines 3 & 4, it will not update the Location along with the Project Name.

All these "s and 's are confusing. My goal is to have it to update multiple fields.

TIA

 

[syoo]

ok i just figured it out

strSQL = "UPDATE Estimates SET [Project Name]= '"
strSQL = strSQL & item.Subject
strSQL = strSQL & "', [Location]= '"
strSQL = strSQL & item.Location
strSQL = strSQL & "'WHERE [EstimateID] = " & mid(Item.UserProperties("ID&quot,4)

this worked.

 

[RJFost]

Speaking as a professional programmer & college instructor, I find the following much easier to code AND read:

strSQL = "UPDATE Estimates " _
& "SET [Project Name]= '" & item.Subject & "', " _
& "[Location]= '" & item.Location & "', " _
& "WHERE [EstimateID] = " & Mid(item.UserProperties("ID&quot, 4)

The text keeps wrapping on that last line; normally, it'd be one line.

Not saying you're wrong, by any means. The example 1) saves all the successive concatenations, 2) makes it easier to keep double & single quotes in order, & 3) arranges the SQL into logical blocks.

 

[zemp]

RJFrost, the successive concatenations that you are saving are being replaced with other concatenations to keep the quotes easier to read. Net gain seems to be zero.

Also the line continuation character can be unstable when dealing with long strings (many fields). Building the string as syoo has done is more stable and less prone to failure.

The only thing that I would add to syoo is to make sure that a space is left/inserted around all key words. Fro example between the last singel quote and the WHERE key word.

One more thing, RJ, You have an extra comma before your WHERE clause.

Thanks and Good Luck!

zemp

 

[syoo]

Thank you both RJ & zemp

 

[RJFost]

Mea culpa on the extra space; I'm on hour 40-some without sleep.

Concatenation by assignment (strSQL = strSQL & ...) involves 2 seperate operations- concatenation & assignment. Using the continuation character, the string is read as a whole; that whole is then assigned. Put both blocks in a module & step through the execution- each line of the build-by-assign is a seperate cycle, whereas the entire continuation block is read in one cycle.

It's basically a matter of style or shop preference; if I seemed to be putting my code out as "the" way, my pardon.

I've built inline SQL (or strings) that ran to over 100 lines & never had a problem. In the rare case where I've had to deal with runtime gargantuan strings, I've found it easiest to store them in Text or Memo (or varchar or whatever) fields & read them in as needed. De gustibus, no disputandem est.