Access Append Query

[texas]

I'm getting a Run-Time error '3134'
Syntax error in the INSERT INTO statement

here is the code:

      sqll = "INSERT INTO TBl_aus_history ( SERVICE_NUM, Paid, DATE_PAID, DATE_RECEIVED, CLAIM_NUM, Member_Name, MEMBER_NUM, PROGRAM_CODE, Provider_Name, PROVIDER_NUM, Refund_Type, Reason_Overpay, Identified_by_Source," & _
      "Memo_a, Dept_Error, Date_Requested, Date_Service, Product, REGION_CODE, [IRS#], Refund_Letter_Type, DATE_EFFECTIVE, [User], Computer, OPID, Date_Rec_Recovery, Provider_Account_Num" & _
      "SELECT Dynamic_Table.SERVICE_NUM, Dynamic_Table.Paid, Dynamic_Table.DATE_PAID, Dynamic_Table.DATE_RECEIVED, Dynamic_Table.CLAIM_NUM, Dynamic_Table.Member_Name, Dynamic_Table.MEMBER_NUM," & _
      "Dynamic_Table.PROGRAM_CODE, Dynamic_Table.Provider_Name, Dynamic_Table.PROVIDER_NUM, [Forms]![Frm_Data_Entry]![cmbRefun_Type] AS Refund_Type," & _
      "[Frm_Data_Entry]![cmbReason_Overpay]) AS Reason_Overpay, [Forms]![Frm_Data_Entry]![cmbSource] AS Identified_by_Source," & _
      "[Forms]![Frm_Data_Entry]![txtmemo] AS Memo_a, [Forms]![Frm_Data_Entry]![cmbDept_Error] AS Dept_Error, CDate([Forms]![Frm_Data_Entry]![txtrequested]) AS Date_Requested," & _
      "CDate([Forms]![Frm_Data_Entry]![txtSdos]) AS Date_Service, [Forms]![Frm_Data_Entry]![cmbProduct] AS Product, Dynamic_Table.REGION_CODE, Dynamic_Table.[IRS#], [Forms]![Frm_Data_Entry]![txtoption] AS Refund_Letter_Type," & _
      "Dynamic_Table.DATE_EFFECTIVE, [Forms]![Frm_Data_Entry]![txtuser] AS [User], [Forms]![Frm_Data_Entry]![txtcomputer] AS Computer, [Forms]![Frm_Data_Entry]![txt_OPID] AS OPID," & _
      "CDate([Forms]![Frm_Data_Entry]![txtDRR]) AS Date_Rec_Recovery, Dynamic_Table.Provider_Account_Num" & _
      "FROM Dynamic_Table"
    
      DoCmd.RunSQL sqll

[M Owen]

You can't embed form variables within a SQL string because the parser WILL NOT look for the values in those forms ... You have to piece the SQL string together with the values yourself.

[texas]

Could you not do it in one of the events of the form?

[MileOut]

Be careful with your commas and spacing.

If you were to display your SQL statement in a message box you would see that the SELECT statement is getting caught:

...Provider_Account_NumSELECT Dynamic_Table...

So, the SELECT statement isn't being found.


Also, a querydef would be better as it will reduce database bloat as the query is already created rather than recreating a query each time. There's no need to in this instance as the query is identical every time. Better to just create it in the QBE.