Getting SQL Syntax Error in INSERT INTO statement in Access 2010

Question

I have written the following Insert Into statement in Access 2010 VBA:

Private Sub AddBPSSButton_Click()

'    CurrentDb.Execute "INSERT INTO TabClearDetail(C_Site) VALUES(" & Me.C_Site & ")"

Dim strSQL As String

'MsgBox Me.[Clearance Applying For]
'MsgBox Me.[Contract Applying for]
'MsgBox Me.[C_Site]
'MsgBox Me.[C_SponsorSurname]
'MsgBox Me.[C_SponsorForename]
'MsgBox Me.[C_SponsorContactDetails]
'MsgBox Me.[C_EmploymentDetail]
'MsgBox Me.[C_SGNumber]
'MsgBox Me.[C_REF1DateRecd]
'MsgBox Me.[C_REF2DateRecd]
'MsgBox Me.[C_IDDateRecd]
'MsgBox Me.[C_IDNum]
'MsgBox Me.[C_CriminalDeclarationDate]
'MsgBox Me.[Credit Check Consent]
'MsgBox Me.[C_CreditCheckDate]
'MsgBox Me.[Referred for Management Decision]
'MsgBox Me.[Management Decision Date]
'MsgBox Me.[C_Comment]
'MsgBox Me.[C_DateCleared]
'MsgBox Me.[C_ClearanceLevel]
'MsgBox Me.[C_ContractAssigned]
'MsgBox Me.[C_ExpiryDate]
'MsgBox Me.[C_LinKRef]
'MsgBox Me.[C_OfficialSecretsDate]

strSQL = "INSERT INTO TabClearDetail(Clearance Applying For, Contract Applying for, " & _
"C_Site, C_SponsorSurname, C_SponsorForename, C_SponsorContactDetails, C_EmploymentDetail, " & _
"C_SGNumber, C_REF1DateRecd, C_RED2DateRecd, C_IDDateRecd, C_IDNum, " & _
"C_CriminalDeclarationDate, Credit Check Consent, C_CreditCheckDate, Referred for Management Decision, " & _
"Management Decision Date, C_Comment, C_DateCleared, C_ClearanceLevel, C_ContractAssigned, " & _
"C_ExpiryDate, C_LinkRef, C_OfficialSecretsDate) VALUES('" & Me.[Clearance Applying For] & "', " & _
"'" & Me.[Contract Applying for] & "', '" & Me.[C_Site] & "', '" & Me.[C_SponsorSurname] & "', " & _
"'" & Me.[C_SponsorForename] & "', '" & Me.[C_SponsorContactDetails] & "', " & _
"'" & Me.[C_EmploymentDetail] & "', '" & Me.[C_SGNumber] & "', '" & Me.[C_REF1DateRecd] & "', " & _
"'" & Me.[C_REF2DateRecd] & "', '" & Me.[C_IDDateRecd] & "', '" & Me.[C_IDNum] & "', " & _
"'" & Me.[C_CriminalDeclarationDate] & "', '" & Me.[Credit Check Consent] & "', '" & Me.[C_CreditCheckDate] & "', " & _
"'" & Me.[Referred for Management Decision] & "', '" & Me.[Management Decision Date] & "', " & _
"'" & Me.[C_Comment] & "', '" & Me.[C_DateCleared] & "', '" & Me.[C_ClearanceLevel] & "', " & _
"'" & Me.[C_ContractAssigned] & "', '" & Me.[C_ExpiryDate] & "', '" & Me.[C_LinKRef] & "', " & _
"'" & Me.[C_OfficialSecretsDate] & "');"

DoCmd.RunSQL (strSQL)

'MsgBox strSQL

End Sub

All The MsgBox calls work, so I believe I have typed all column names and text box names correctly. I am getting a Syntax error when I get to the DoCmd.RunSQL line. Have been staring at this for quite a while trying to see if I have missed a comma or speech mark or something, but am hoping maybe another set of eyes will see my mistake.

Here is the MsgBox that is returned

Any help will be greatly appreciated.

Thanks!

Seeing the output of MsgBox strSQLwould be helpful i think — Martin K.
– Martin K., Commented Jun 12, 2014 at 9:13
Be aware that after you change your SQL statement to put square brackets around the field names that contain spaces and get the statement to run it will mangle your dates. 30/06/2014 will be interpreted as "June 30, 2014", but 03/06/2014 will be interpreted as "March 6, 2014", NOT "June 3, 2014". — Gord Thompson
– Gord Thompson, Commented Jun 12, 2014 at 17:25

GarethD · Accepted Answer · 2014-06-12 09:22:01Z

2

When you have spaces in your object names you need to enclose the full name in brackets. So where you have:

INSERT INTO TabClearDetail(Clearance Applying For, Contract Applying for,...

It should be

INSERT INTO TabClearDetail([Clearance Applying For], [Contract Applying for],...

For what it's worth I personally can't stand special characters in object names for exactly this reason, if it's not too late you should consider renaming your columns. Pascal Case is usually clear enough for compound names, e.g. ClearanceApplyingFor. Or use under scores - Clearance_Applying_For.

I also think the convention in Access is to qualify dates with # rather than single quotes, i.e. #01/01/2014#, instead of `01/01/2014'

edited Jun 12, 2014 at 9:22

answered Jun 12, 2014 at 9:16

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hello123 Over a year ago

I've done this and noticed it should be C_REF2DateRecd not C_RED2DateRecd

Visual Studio Guy · Accepted Answer · 2014-06-12 09:20:55Z

0

Either you try enclosing the date fields with "#" instead of "'" or change your database's "SQL Server Compatible Syntax" property.

answered Jun 12, 2014 at 9:20

Visual Studio Guy

1441 silver badge3 bronze badges

Collectives™ on Stack Overflow

Getting SQL Syntax Error in INSERT INTO statement in Access 2010

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related