append query syntax error

[mafhobb]

Syntax error in this append query. What is it?

        Dim DataToAdd As String
        DataToAdd = "INSERT INTO Address " & _
        "customerId, addressNr, addressType, firstname, lastname, companyName, postalcode, country, workphone, email, notes, streetaddress, city, contactTypeId " & _
        "SELECT customerId, addressNr, addressType, firstname, lastname, companyName, postalcode, country, workphone, email, notes, streetaddress, city, contactTypeId " & _
        "FROM Customer WHERE ((Customer.function)=ADD);"
        Debug.Print DataToAdd
        CurrentDb.Execute DataToAdd, dbFailOnError

I am trying to append data from the query into the table where the value on function field in the query is equal to the word ADD

mafhobb

 

[Minty]

You don't have the insert syntax correct - see http://www.w3schools.com/sql/sql_insert.asp for help

 

[mafhobb]

I am just trying to write in code this SQL query

INSERT INTO Address ( customerId, addressNr, addressType, firstname, lastname, companyName, postalcode, country, workphone, email, notes, streetaddress, city, contactTypeId )
SELECT Customer.customerId, Customer.addressNr, Customer.addressType, Customer.firstname, Customer.lastname, Customer.companyName, Customer.postalcode, Customer.country, Customer.workphone, Customer.email, Customer.notes, Customer.streetaddress, Customer.city, Customer.contactTypeId
FROM Customer
WHERE (((Customer.function)="INSERT"));

Which works just fine

 

[Uncle Gizmo]

You appear to have one less fields in your insert statement than you have in your select statement.

 

[mafhobb]

There should be 14 fields on each statement. Is there something written wrong?

 

[Uncle Gizmo]

No it's me I can't count!

 

[Minty]

Isn't the syntax supposed to be

INSERT INTO tablename (field1, field2 etc)
VALUES (Value1, value2 etc)
WHERE criteria goes here

?

 

[mafhobb]

This works:

        CurrentDb.Execute "INSERT INTO Address ( customerId, addressNr, addressType, firstname, lastname, companyName, postalcode, country, workphone, email, notes, streetaddress, city, contactTypeId )" & _
        "SELECT Customer.customerId, Customer.addressNr, Customer.addressType, Customer.firstname, Customer.lastname, Customer.companyName, Customer.postalcode, Customer.country, Customer.workphone, Customer.email, Customer.notes, Customer.streetaddress, Customer.city, Customer.contactTypeId " & _
        "FROM Customer " & _
        "WHERE (Customer.function='ADD');"