spot syntax error in access sql

pwd

urgh tired please help

Seth Brundle

Is this an error message for an insert & select statement?
Should the word 'value' be in the square brackets (addresses.[new id value]) at the end?

pwd

it's a msgbox showing the sql string generated by a vba class right before it's run as sql and generates a syntax error.

so this is the actual sql code I'm trying to execute, not an error message.

Don't think value should be put in the end. The contents of the square brackets are the names of the fields in the table being referenced.

working at home + recent installation of broadband in flat = having to stay up late cos I haven't done my work

Seth Brundle

Are you inserting data into a table (callouts for duplicates), selecting data from a table (addresses) or are you trying both?
try reading www.w3schools.com/sql

seamus

remove "addresses.[new id value]" and replace it with just "[new id value]"

pwd

there shouldn't be a comma before the select statement! silly me
thanks for responses

Seth Brundle

@ seamus - thats not going to change much though. The word value shouldn't be there and it begins as an insert statement but before the 'values' word is used he begins a select statement

pwd

have that tutorial open most of the time I work on this sort of thing!
I'm using the select as a subquery though which is beyond the scope of it.
ie: I'm selecting the values from the addresses table and inserting them into certain fields of the [callout for duplicates] table.

Seth Brundle

ok right then you seem to be on the right track
INSERT INTO table1 (col1, col2, col3,...)
SELECT (col1, col2, col3,...) FROM table2;

pwd

yeah I was trying to simultaeneously insert values and a subquery which it didn't like. Instead I did the insert...select and then an update to put in the other values.
this seems to work:


strsql = "insert into [callouts for duplicates] (account, [address 1], [address 2], [address 3]) select [account no], [addr1], [addr2], [addr3] from addresses where [new ID value] = '" + List0.Column(1, i) + "';"
MsgBox (strsql)
DoCmd.RunSQL (strsql)
strsql = "update [callouts for duplicates] set [BIN] = '" + List0.Column(0, i) + "' where account = '" + List0.Column(1, i) + "';" ',[Address 1], [Address 2], [Address 3] from addresses where addresses.[new id value] =" + List0.Column(2, i) + ")"
MsgBox (strsql)
DoCmd.RunSQL (strsql)

dangerduckula

Try this code:

strSql = "INSERT INTO Wages (surveyors) VALUES ('More Money')"

:-)