Problem with Insert Statement strSQL = "INSERT INTO (1 Viewer)

Battlescar

New member
Local time
Today, 13:28
Joined
Nov 19, 2017
Messages
3
I get an run-time error '3134' syntax error in insert into statement.
I have no idea why. I am still just learning.:banghead:


Private Sub cmdExport_Click()
Dim FN As String
Dim intReply As Integer
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb

strSQL = "INSERT INTO tbl_Export (SurveyListID_PK, SurveyName, SurveyStatus, SurveyType, OriginalEQCollectionStartDate, OIDCoordinator, Newlyadded, Lastmodifieddate, SurveyFrequency, SDDS, Field, Division, FundingType, Attachments, AlternativeData, AdministrativeData, FundingStatus, Recurring, PECodeInitialDevel, [PECodeE&M], [FY 15/16], [Kick off_date1516], [Design Start1516], [QDRC Testing Date1516], [UAT Start1516], [Collection Start1516]" & _
"SELECT tblsurveylist.SurveyListID_PK, tblsurveylist.SurveyName, tblsurveylist.SurveyStatus, tblsurveylist.SurveyType, tblsurveylist.OriginalEQCollectionStartDate, tblsurveylist.OIDCoordinator, tblsurveylist.Newlyadded, tblsurveylist.Lastmodifieddate, tblsurveylist.SurveyFrequency, tblsurveylist.SDDS, tblsurveylist.Field, tblsurveylist.Division, tblsurveylist.FundingType, tblsurveylist.Attachments, tblsurveylist.AlternativeData, tblsurveylist.AdministrativeData, tblsurveylist.FundingStatus, tblsurveylist.Recurring, tblsurveylist.PECodeInitialDevel, tblsurveylist.[PECodeE&M], tblSurveylistSub.FY, tblSurveylistSub.KickOffDate, tblSurveylistSub.DesignStart, tblSurveylistSub.QDRC_TestingDate, tblSurveylistSub.UAT_Start, tblSurveylistSub.CollectionStart" & _
"FROM tblsurveylist LEFT JOIN tblSurveylistSub ON tblsurveylist.SurveyListID_PK = tblSurveylistSub.SurveyListID_PK) LEFT JOIN tbl_Export ON tblsurveylist.SurveyListID_PK = tbl_Export.SurveyListID_PK" & _
"where tblSurveylistsub.FiscalYear = [FY 15/16]"
db.Execute strSQL, dbFailOnError
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 13:28
Joined
Oct 17, 2012
Messages
3,276
The first step you should always do when troubleshooting a problem like this is to print out the completed SQL, as that will often make it obvious what your problem is.

In this case:
  1. Your insert clause lacks a closing parenthesis.
  2. There needs to be a space between the end of your insert clause and 'SELECT'.
  3. There needs to be a space between the end of your select clause and 'FROM'.
  4. There needs to be a space between the end of your from clause and 'WHERE'.
Those are the obvious ones that jumped out at me.
 

jleach

Registered User.
Local time
Today, 13:28
Joined
Jan 4, 2012
Messages
308
Print it out then throw it into Notepad++ and use Poor-Man's T-SQL Formatter to make it readable!

Screenshot 2017-12-08 12.49.59.png
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 10:28
Joined
Sep 12, 2017
Messages
2,111
From the looks of it, you will also want to address your naming conventions AND look at data normalization.

[FY 15/16] screams that you are either not using this field for FY15/16 data OR you really need to move it to a child table.

You will also want to remove special characters from your field names.
 

Users who are viewing this thread

Top Bottom