Compile Error : Expected : end of statement (1 Viewer)

[Hansi]

Hi I design SQL statement in access and now i want it run through button using VBA. but I got end of statement. I don't have idea to fix it.

Dim sqlImport As String
           
sqlImport = "" 
sqlImport = sqlImport & "INSERT INTO Main ( Report_Date, LEA, CRM_ORDER, SO_ID, SO_STATUS, TASK_NAME, TASK_STATUS, TASK_WG, ASSIGNED_DATE, CLOSED_DATE, SERVICE_TYPE, ORDER_TYPE, ACCOUNT_NO )"
sqlImport = sqlImport & "SELECT Main.Report_Date, [All Detail].LEA, [All Detail].CRM_ORDER, [All Detail].SO_ID, [All Detail].SO_STATUS, [All Detail].TASK_NAME, [All Detail].TASK_STATUS, [All Detail].TASK_WG, [All Detail].ASSIGNED_DATE, [All Detail].CLOSED_DATE, [All Detail].SERVICE_TYPE, [All Detail].ORDER_TYPE, [All Detail].ACCOUNT_NO"
sqlImport = sqlImport & "FROM [All Detail], Main"
sqlImport = sqlImport & "WHERE (((Main.Report_Date)='"&Me.rd_&[COLOR=Red]"'))"[/COLOR]
sqlImport = sqlImport & ";"

 Debug.Print sqlImport
DoCmd.RunSQL sqlImport

 

[isladogs]

You need a space before SELECT, FROM, WHERE on each line.

If it still fails, copy the debug result from the immediate window into a query and run it.
If it fails it should pinpoint the section that is a problem

BTW there is no join between your tables so you will get all results from each table when it does work

 

[Hansi]

)Thank you Colin it corrected by,
"WHERE Main.[Report_Date]='" & Me.rd.Value & "'".
But what you mean by no join between tables?? I am sorry I am new to access VBA.

 

[isladogs]

FYI you don't need .Value as that's the default.

Say you have 2 tables with 4 records & 8 records respectively, both with an autonumber ID primary key field

There are 4 possible ways of combining these tables in a query

1. using an INNER join - you get 4 records - as only those records with ID values in both tables are used

2. using a LEFT OUTER join with all records from Table 3 - once again 4 records in query

3. using a RIGHT OUTER join with all records from table 4 - this time 8 records in query

3. NO join (AKA a cartesian join) - here you get every combination of records. In this case 4x8=32

This last option should only be used in limited circumstances and for specific purposes

HTH

 

[Hansi]

I got it thank you Colin.