syntax error in INSERT INTO

[sachin]

I am getting the errorr syntax errorr in INSERT INTO statement when running this through access vb but when I run the same query (got from debug.print cQuery) in access query section it appends the records in table. Can anybody help me out on this problem.
Private Sub CmdRpt_Enter()

daoConn1 = "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=I:\Deepak nwsppr with duplicate bill\newspapers.mdb;Data Provider=Microsoft.Jet.OLEDB.4.0"

daoConn1.Open

Smonth = MonthName(Month(DTPicker0.Value))
SYear = Year(Format(DTPicker0.Value, "dd/mm/yyyy"))

CQuery = " Delete from DuplicateBill_Report"

daoConn1.Execute CQuery

CQuery = ""
CQuery = "Insert into DuplicateBill_Report (Cust_ID,Cust_name,Cust_BlockNo,Cust_Add1,Cust_Add2,Cust_Add3,Delvery_charges1,Paper_name1,Quantity1,Rate,Amount1,Balance1,Line_ID,Line_Name,Month,Year) "
CQuery = CQuery & " select Cust_ID,Cust_name,Cust_BlockNo,Cust_Add1,Cust_Add2,Cust_Add3,Delvery_charges1,Paper_name1,Quantity1,Rate,Amount1,Balance1,Line_ID,Line_Name,Month,Year from duplicate_bill "
CQuery = CQuery & " where Month = '" & Smonth
CQuery = CQuery & "' and Year = " & SYear

Debug.Print CQuery

daoConn1.Execute CQuery
daoConn1.Close

End Sub

 

[boblarson]

Okay, a couple of things for you.

1. You don't need the connection stuff.

You can simply use

Dim db As DAO.Database

Set db = OpenDatabase("I:\Deepak nwsppr with duplicate bill\newspapers.mdb")

2. You were told in your last post that Month and Year are Access reserved words and therefore NEED to be in SQUARE BRACKETS.

CQuery = "Insert into DuplicateBill_Report (Cust_ID,Cust_name,Cust_BlockNo,Cust_Add1,Cust_Add 2,Cust_Add3,Delvery_charges1,Paper_name1,Quantity1 ,Rate,Amount1,Balance1,Line_ID,Line_Name,[COLOR="red"][B][[/B][/COLOR]Month[COLOR="red"][B]][/B][/COLOR],[COLOR="red"][B][[/B][/COLOR]Year[B][COLOR="Red"]][/COLOR][/B]) "

CQuery = CQuery & " [COLOR="red"][B]([/B][/COLOR]SELECT Cust_ID,Cust_name,Cust_BlockNo,Cust_Add1,Cust_Add2 ,Cust_Add3,Delvery_charges1,Paper_name1,Quantity1, Rate,Amount1,Balance1,Line_ID,Line_Name,Month,Year from duplicate_bill "
CQuery = CQuery & " where [COLOR="red"][B][[/B][/COLOR]Month[COLOR="red"][B]][/B][/COLOR] = '" & Smonth
CQuery = CQuery & "' and [COLOR="red"][B][[/B][/COLOR]Year[COLOR="red"][B]][/B][/COLOR] = " & SYear[B][COLOR="red"])[/COLOR][/B]

So to execute them (if you use my simple OpenDatabase method) you can use

db.Execute CQuery