 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / January 2007Syntax error for INSERT INTO WHERE statement
Been a tough week for bugs. This code looks good but fails. Any insights would be appreciated Dim strQuote As String Dim str As String Dim strQuote3 As Long str = "INSERT INTO 2007TourneyResults([week2]) VALUES " _ & strQuote3 & " WHERE [playerid] = '" & strQuote & "'" Debug.Print str CurrentDb.Execute str, dbFailOnError table 2007TourneyResults field week2 long integer field playerid text here's the string it's returning INSERT INTO 2007TourneyResults([week2]) VALUES 630000 WHERE [playerid] = '00/65/67/' You don't tell us what data type the [week2] field is -- text? long integer? integer? other? What is the purpose of the WHERE clause? The SQL statement appears as if you just want to add a single, new record to the table, with just a value for the [week2] field. You don't use a WHERE clause in this situation. I am guessing that that is not what you want to do, as I assume there are other fields in the table that also should be getting records. By chance are you wanting to update the [week2] value for an existing record that is in the table, namely the value in the record where [playerid] (which I assume is the primary key) has the value '00/65/67/' ? You need to tell us more details, please.  SignatureKen Snell <MS ACCESS MVP> > Been a tough week for bugs. This code looks good but fails. Any insights > would be appreciated [quoted text clipped - 16 lines] > INSERT INTO 2007TourneyResults([week2]) VALUES 630000 WHERE [playerid] = > '00/65/67/' Thanks for responding Ken actually I did field week2 (is a) long integer and you are correct, I am inserting a value in an existing table and field based on the playerid criteria, it's actually based on a web scraper db that Doug Steele built, I am looping through a series of values on a webpage and then inserting the sole field2 value for each of a series of playerid criteria hopefully that gives you enough... forgive my noobiness! >You don't tell us what data type the [week2] field is -- text? long integer? >integer? other? [quoted text clipped - 15 lines] >> INSERT INTO 2007TourneyResults([week2]) VALUES 630000 WHERE [playerid] = >> '00/65/67/' Comments inline... SignatureKen Snell <MS ACCESS MVP> > Thanks for responding Ken > > actually I did > field week2 (is a) long integer Correct, you did. My error -- it's been a long weekend already. < g > > and you are correct, I am inserting a value in an existing table and field > based on the playerid criteria, it's actually based on a web scraper db [quoted text clipped - 3 lines] > then inserting the sole field2 value for each of a series of playerid > criteria So you want to insert a new record with the value of the [week2] field and the [playerid] field being inserted? OK, then your code step would be this: str = "INSERT INTO 2007TourneyResults ([week2], [playerid) VALUES (" _ & strQuote3 & ", '" & strQuote & "');" > hopefully that gives you enough... forgive my noobiness! Only if you forgive my misreading of your original post < G >. >>You don't tell us what data type the [week2] field is -- text? long >>integer? [quoted text clipped - 18 lines] >>> INSERT INTO 2007TourneyResults([week2]) VALUES 630000 WHERE [playerid] = >>> '00/65/67/' thanks Ken, sorry if I didn't explain myself correctly but the table and field exists already, and the current value of field week2 is 0 and I would like to update it to 630000 (a new value to overwrite 0) for playerid 00/65/67/ (an existing value, I do not want to add to the table) when I use your code I get error 6 overflow >Comments inline... >> Thanks for responding Ken [quoted text clipped - 25 lines] >>>> INSERT INTO 2007TourneyResults([week2]) VALUES 630000 WHERE [playerid] = >>>> '00/65/67/' as soon as I typed the work UPDATE it struck me where I went wrong str = "UPDATE 2007TourneyResults SET week2 = " & strQuote3 & " WHERE playerid = '" & strQuote & "'" thanks Ken, the dialoguing helped!! >thanks Ken, sorry if I didn't explain myself correctly but > [quoted text clipped - 8 lines] >>>>> INSERT INTO 2007TourneyResults([week2]) VALUES 630000 WHERE [playerid] = >>>>> '00/65/67/' I was wondering if you were wanting to update and not insert. Glad you got the solution. SignatureKen Snell <MS ACCESS MVP> > as soon as I typed the work UPDATE it struck me where I went wrong > > str = "UPDATE 2007TourneyResults SET week2 = " & strQuote3 & " WHERE > playerid = '" & strQuote & "'" > > thanks Ken, the dialoguing helped!! Hi, What Ken is saying is that your syntax was wrong. This is the correct syntax for inserting one record. insert into "tablename" (first_column,...last_column) values (first_value,...last_value); If wish to use the “Where” condition, include the subquery like, INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase] SELECT [source.]field1[, field2[, ...] FROM tableexpression WHERE [playerid] = '00/65/67/' >pubdude2003 wrote: >thanks Ken, sorry if I didn't explain myself correctly but [quoted text clipped - 3 lines] >playerid 00/65/67/ (an existing value, I do not want to add to the table) >when I use your code I get error 6 overflow Thanks AccessVandal, appreciate your post! As happens so many times when posting to this invaluable resource, just the dialogue seems to help. I was using the wrong statement from the get go. I posted the solution just one minute before your post.... thanks for the input everyone. >Hi, > [quoted text clipped - 18 lines] >>playerid 00/65/67/ (an existing value, I do not want to add to the table) >>when I use your code I get error 6 overflow |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.