SQL query in VB - error

[mazza]

I keep getting an error message when running the bolow code? Any idea why?



strSQL = "INSERT INTO TblTempContractInvoices (TblContract.nextInvoiceDate, TblContract.Contractnr, TblContract.CustomerId, TblContract.End, " & _
" TblContract.contractvalue, TblContract.Invoicecycle, TblContract.compname, TblContract.compaddress, TblContract.compcity, " & _
" TblContract.comppostcode,TblContract.advance, TblContract.ContractType) " & _
" FROM TblContract " & _
" WHERE (((TblContract.nextInvoiceDate)<#" & [Forms]![FrmInvoiceContract]![DateTo] & "#) " & _
" AND ((TblContract.End) Is Null)) OR (((TblContract.nextInvoiceDate)<#" & [Forms]![FrmInvoiceContract]![DateTo] & "#) " & _
" AND ((TblContract.End)>#" & [nextInvoiceDate] & "#)); "

DoCmd.RunSQL strSQL

 

[pbaldy]

Sure; bad syntax.

INSERT INTO TableName(Fields within THAT table)
SELECT fields from another table
FROM AnotherTable
WHERE ...

 

[mazza]

No doubt my syntact has errors, have changed it but still get the error message

This was the original query created in access SQL View
INSERT INTO TblTempContractInvoices ( nextInvoiceDate, Contractnr, CustomerId, [End], Invoicevalue, Invoicecycle, compname, compaddress, compcity, comppostcode, advance, ContractType, nextperiod )
SELECT TblContract.nextInvoiceDate, TblContract.Contractnr, TblContract.CustomerId, TblContract.End, TblContract.Invoicevalue, TblContract.Invoicecycle, TblContract.compname, TblContract.compaddress, TblContract.compcity, TblContract.comppostcode, TblContract.advance, TblContract.ContractType, IIf([InvoiceCycle]=2,DateSerial(Year([nextInvoiceDate]),Month([nextInvoiceDate])+1,Day([nextInvoiceDate])),IIf([InvoiceCycle]=3,DateSerial(Year([nextInvoiceDate]),Month([nextInvoiceDate])+3,Day([nextInvoiceDate])),IIf([InvoiceCycle]=4,DateSerial(Year([nextInvoiceDate])+1,Month([nextInvoiceDate]),Day([nextInvoiceDate])),""))) AS NextPeriod
FROM TblContract
WHERE (((TblContract.nextInvoiceDate)<[Forms]![FrmInvoiceContract]![dateTo]) AND ((TblContract.End) Is Null)) OR (((TblContract.nextInvoiceDate)<[Forms]![FrmInvoiceContract]![dateTo]) AND ((TblContract.End)>[nextinvoicedate]));

----

This is the code I entered in vb as an on clcik event
--------

strSQL = "INSERT INTO TblTempContractInvoices ( nextInvoiceDate, Contractnr, CustomerId, [End], Invoicevalue, Invoicecycle, compname, compaddress, compcity, comppostcode, advance, ContractType, nextperiod ) " & _
" SELECT TblContract.nextInvoiceDate, TblContract.Contractnr, TblContract.CustomerId, TblContract.End, TblContract.Invoicevalue, TblContract.Invoicecycle, TblContract.compname, TblContract.compaddress, TblContract.compcity, TblContract.comppostcode, TblContract.advance, TblContract.ContractType, IIf([InvoiceCycle]=2,DateSerial(Year([nextInvoiceDate]),Month([nextInvoiceDate])+1,Day([nextInvoiceDate])),IIf([InvoiceCycle]=3,DateSerial(Year([nextInvoiceDate]),Month([nextInvoiceDate])+3,Day([nextInvoiceDate])),IIf([InvoiceCycle]=4,DateSerial(Year([nextInvoiceDate])+1,Month([nextInvoiceDate]),Day([nextInvoiceDate])),""))) AS NextPeriod " & _
" FROM TblContract " & _
" WHERE (((TblContract.nextInvoiceDate)<#" & [Forms]![FrmInvoiceContract]![DateTo] & "#) " & _
" AND ((TblContract.End) Is Null)) OR (((TblContract.nextInvoiceDate)<#" & [Forms]![FrmInvoiceContract]![DateTo] & "#) " & _
" AND ((TblContract.End)>#" & [nextInvoiceDate] & "#)); "

 

[Alc]

What's the error message?

 

[modest]

Are you making a new table? You could just use SELECT INTO and make things simpler. Otherwise, post your error code.

 

[mazza]

error message
runtime error 2465
ms access cannot find the field "¦" in your expression

just trying to append fields to an existing table