Syntax error for INSERT INTO WHERE statement

[pubdude2003 via AccessMonster.com]

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/'

 

[Ken Snell \(MVP\)]

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.

 

[pubdude2003 via AccessMonster.com]

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?

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.

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/'

 

[Ken Snell \(MVP\)]

Comments inline...
--

Ken 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
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

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!

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.

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/'

 

[pubdude2003 via AccessMonster.com]

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

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

[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!

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/'

 

[pubdude2003 via AccessMonster.com]

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

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/'

 

[AccessVandal via AccessMonster.com]

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 via AccessMonster.com]

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,

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

 

[Ken Snell \(MVP\)]

I was wondering if you were wanting to update and not insert. Glad you got
the solution.