VBA Syntax For Append Query

[jo15765]

I am geting a syntax error with my syntax. I am sure it is something simple, but I have added double quotes and removed them but can't get the syntax perfect. How should this be changed so it is a valid VBA syntax?

appSQL = "INSERT INTO ProdData ( ICC, Description, SourceInfo, MinQTY, PriceBreal, salesRep ) " & _
            "SELECT [NIT].ICC, [ItemCode] & ' ' & [Description] AS FullDescription, "& SourceInfo & "," & MinQTY & "," & PriceBreal & "," & salesRep & " & _
            "FROM NIT;"

[aytee111]

You need an AS fieldname for your second field. Add a line just after it Debug.Print appSQL and see what it produces.

[jo15765]

I am wanting to concatenate the 2nd & 3rd together.
[ItemCode] & ' ' & [Description] AS FullDescription

[aytee111]

That's field 1. The second field is also a concatenation but there is no field name.

[jo15765]

Unless I am mising the obvious here, wouldn't field one be [NIT].ICC and field 2 would be [ItemCode] & ' ' & [Description] AS FullDescription

[aytee111]

Query field name, not table field name.

[aytee111]

Sorry, field 3.

[jwhite]

PMFJI... You have extraneous characters at the end of the second line: salesRep & " & _

Try removing & " directly after salesRep

[jo15765]

PMFJI... You have extraneous characters at the end of the second line: salesRep & " & _

Try removing & " directly after salesRep

That gives an error of Compile error syntax error on the after the field I removed. " &

[jo15765]

Query field name, not table field name.

Addingin an "As" and aliasing my variables for names now removes the error

[Bulzie]

You need the & _ at the end to get the From clause on the next line.

Try copying that code into a new query in the SQL designer, then select the Design look and see where it is placing the fields and criteria. Try running it from that query designer and if works, can view the SQL code and use that in VBA if you want. You can also leave it as a Query and do Docmd.RunQuery "YourAppendQuery"

[Bulzie]

Also on the Select maybe:
"SELECT [NIT].ICC, [ItemCode] & ' ' & [Description] AS FullDescription, [SourceInfo], [MinQTY], [PriceBreal], [salesRep] From NIT;"

[Micron]

I don't understand all the concatenation after the desired concatenation of the 2nd and 3rd field. If you string the rest together, the number of selected fields doesn't match the number of fields listed in the insert portion. Maybe I'm interpreting it wrong, but isn't it more like

appSQL = "INSERT INTO ProdData (ICC, Description, SourceInfo, MinQTY, PriceBreal, salesRep) " & _
        "SELECT [NIT].ICC, ([ItemCode] & ' ' & [Description] AS FullDescription), SourceInfo, MinQTY, PriceBreal, salesRep FROM NIT;"