query runs in access query section not through code

[sachin]

i get syntax error in following code but runs perfectly ok in access query section can anybody help me

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

 

[G81]

looks like you might be missing single quotes for the SYear string

so instead of:

CQuery = CQuery & "' and Year = " & SYear

should be:

CQuery = CQuery & "' and Year = '" & SYear & "'"

 

[boblarson]

By the way, MONTH and YEAR are ACCESS RESERVED WORDS and as such will need to be in square brackets when referred to as table/query fields.

 

[vbaInet]

...and why not just store just the Date and extract the Month and Year in a query for when you want to use them.