SQL syntax help please
in [General]
|
From: Sue on 1 Mar 2010 06:45 Hi all I had an update sql statement working setting a value to true (boolean field) on a set condition, but I am now trying to update a 2nd field also which is text type and I can't seem to get the syntax right. I get 'expected end of statement' message. Code is: strSQL = "UPDATE [qry Resource Planning VVs] " & _ "SET [Selected] = True & _ "[Activity] = '"VV"', " & _ "WHERE Territory_Code = " & """" & txtTerritory & """" Can anyone please help correct this? Thanks...
From: Stefan Hoffmann on 1 Mar 2010 07:16 hi Sue, On 01.03.2010 12:45, Sue wrote: > I get 'expected end of statement' message. Code is: > > strSQL = "UPDATE [qry Resource Planning VVs] "& _ > "SET [Selected] = True& _ > "[Activity] = '"VV"', "& _ > "WHERE Territory_Code = "& """"& txtTerritory& """" There is a comma missing and the quotation marks are also wrong: strSQL = _ "UPDATE [qry Resource Planning VVs] " & _ "SET [Selected] = True, " & _ "[Activity] = 'VV' " & _ "WHERE [Territory_Code] = '" & Replace(txtTerritory, "'", "''") & "'" mfG --> stefan <--
From: MikeR on 1 Mar 2010 07:28 Sue wrote: > Hi all > > I had an update sql statement working setting a value to true (boolean > field) on a set condition, but I am now trying to update a 2nd field also > which is text type and I can't seem to get the syntax right. I get 'expected > end of statement' message. Code is: > > strSQL = "UPDATE [qry Resource Planning VVs] " & _ > "SET [Selected] = True & _ > "[Activity] = '"VV"', " & _ > "WHERE Territory_Code = " & """" & txtTerritory & """" > > Can anyone please help correct this? Thanks... > > Maybe "SET [Selected] = True " & _ (missing closing quote). Mike
From: Sue on 1 Mar 2010 08:58 Thanks Stefan that sorted it :-). I changed the quotes and added the comma. I left the where clause alone though as this was working ok before. Thanks again for the help!! "Stefan Hoffmann" <ste5an(a)ste5an.de> wrote in message news:Oxmh$jTuKHA.732(a)TK2MSFTNGP06.phx.gbl... > hi Sue, > > On 01.03.2010 12:45, Sue wrote: >> I get 'expected end of statement' message. Code is: >> >> strSQL = "UPDATE [qry Resource Planning VVs] "& _ >> "SET [Selected] = True& _ >> "[Activity] = '"VV"', "& _ >> "WHERE Territory_Code = "& """"& txtTerritory& >> """" > There is a comma missing and the quotation marks are also wrong: > > strSQL = _ > "UPDATE [qry Resource Planning VVs] " & _ > "SET [Selected] = True, " & _ > "[Activity] = 'VV' " & _ > "WHERE [Territory_Code] = '" & Replace(txtTerritory, "'", "''") & "'" > > > mfG > --> stefan <--
From: Stefan Hoffmann on 1 Mar 2010 09:10
hi Sue, On 01.03.2010 14:58, Sue wrote: > Thanks Stefan that sorted it :-). I changed the quotes and added the comma. > I left the where clause alone though as this was working ok before. Thanks > again for the help!! Just enter a " in your txtTerritory before executing your code. Test it... mfG --> stefan <-- |