Syntax error in Insert Into statement

G

Guest

I made a VBA code with duplication of an example CD. I only changed the field
name. But it can not work. It shows: “Syntax error in INSERT INTO statementâ€.

My VBA code is as below:
Private Sub Save_Record_Click()
On Error GoTo Err_Save_Record_Click
Dim STemp As String
If Me!EmployeeID <> "" And Me!DeptID <> "" And Me!StartDate <> "" And
Me!EndDate <> "" And Me!TotalOutTime <> "" Then
STemp = "Insert Into [Total Record] "
STemp = STemp & "(EmployeeID,DeptID,StartDate,EndDate,OutTime,"
STemp = STemp & "TotalOutTime,LeaveTime,TotalLeaveTime,OTTime,"
STemp = STemp & "TotalOTime,LateTime,EarlyLeaveTime,LackTime,Note) "
STemp = STemp & "Values ('" & Me!EmployeeID & "','" & Me!DeptID & "',"
STemp = STemp & "'#" & Me!StartDate & "#','#" & Me!EndDate & "#',"
STemp = STemp & "'" & Me!OutTime & "','" & Me!TotalOutTime & "',"
STemp = STemp & "'" & Me!LeaveTime & "','" & Me!TotalLeaveTime & "',"
STemp = STemp & "'" & Me!OTTime & "','" & Me!TotalOTime & "',"
STemp = STemp & "'" & Me!LateTime & "','" & Me!EarlyLeaveTime & "',"
STemp = STemp & "'" & Me!LackTime & "','" & Me!Note & "')"
DoCmd.RunSQL STemp
MsgBox "Save Record Successfully", vbOKOnly, "Save Completely"
Else
MsgBox "EmployeeID should not be empty,please input record!", vbOKOnly,
"Warning"
Me!EmployeeID.SetFocus
End If
Exit_Save_Record_Click:
Exit Sub
Err_Save_Record_Click:
MsgBox Err.Description
Resume Exit_Save_Record_Click
End Sub

The code is used for inserting form value into table in ACCESS 2003. Would
you please help me check if there is any problem with the code?
 
J

John Vinson

I made a VBA code with duplication of an example CD. I only changed the field
name. But it can not work. It shows: “Syntax error in INSERT INTO statement”.
Click to expand...

I'd suggest that you go into Debug mode; set a breakpoint in the code,
step through until your SQL string is built, and type

?STemp

in the Immediate window. Copy and paste the SQL string into the SQL
window of a new blank query, and try running it there to see what the
error might be.

The error could very well be data dependent, e.g. inserting an invalid
date.

John W. Vinson[MVP]
 
G

Guest

John:
I have tried as you instructed. I inserted the following code into a new
query: Insert Into [Total Record]
(EmployeeID,DeptID,StartDate,EndDate,OutTime,TotalOutTime,LeaveTime,TotalLeaveTime,OTTime,TotalOTime,LateTime,EarlyLeaveTime,LackTime,Note)
Values
('0003','06','#1/1/2000#','#1/1/2007#','2','184d','2','14Day','1','5Hour30Minute','4','0','1622','OK')
It shows the same problem and also emphasize on "Note" field. "Note" field
is a text field. I don't know what's wrong with this field.
 
V

Van T. Dinh

Don't enlcose every explicit value in single quotes. Use the following:

1. Text value: delimited by single-quotes or double quotes.

2. DateTime value: delimited by hashes (#) and the date in US format
"mm/dd/yyyy hh:nn:ss"
or an internationally unambiguous format like "yyyy-mm-dd HH:nn:ss".

3. Numeric value: no delimiters use.

I think you construction should result to something like:

====
INSERT INTO [Total Record]
(EmployeeID, DeptID, StartDate, EndDate, OutTime, TotalOutTime,
LeaveTime, TotalLeaveTime, OTTime, TotalOTime, LateTime,
EarlyLeaveTime, LackTime, Note)
Values
(3, 6, #1/1/2000#, #1/1/2007#, 2, ???,
2, ???, 1, ???,
4, 0, 1622, 'OK')
====

I don't know what you want to do with '184d', '14Day', '5Hour30Minute' since
I don't think JET will recognize these as durations!
 
D

Douglas J. Steele

What happens if you put in spaces between the table name and the list of
fields, and between the list of fields and the keyword VALUES?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shell said:
Van:
Thanks for your response!
I have done like you instructed me as following:
INSERT INTO [Total
Record](EmployeeID,DeptID,StartDate,EndDate,OutTime,TotalOutTime,LeaveTime,TotalLeaveTime,OTTime,TotalOTime,LateTime,EarlyLeaveTime,LackTime,Note)VALUES
( '0003
','06',#1/1/2000#,#1/1/2007#,2,'184d',2,'14Day',1,'5Hour30Minute',4,0,1622,'')
I have set "TotalOutTime" field, "TotalLeaveTime" field and "TotalOTime"
field as text field. But the result is the same:Syntax error in Insert
Into
statement.

Van T. Dinh said:
Don't enlcose every explicit value in single quotes. Use the following:

1. Text value: delimited by single-quotes or double quotes.

2. DateTime value: delimited by hashes (#) and the date in US format
"mm/dd/yyyy hh:nn:ss"
or an internationally unambiguous format like "yyyy-mm-dd HH:nn:ss".

3. Numeric value: no delimiters use.

I think you construction should result to something like:

====
INSERT INTO [Total Record]
(EmployeeID, DeptID, StartDate, EndDate, OutTime, TotalOutTime,
LeaveTime, TotalLeaveTime, OTTime, TotalOTime, LateTime,
EarlyLeaveTime, LackTime, Note)
Values
(3, 6, #1/1/2000#, #1/1/2007#, 2, ???,
2, ???, 1, ???,
4, 0, 1622, 'OK')
====

I don't know what you want to do with '184d', '14Day', '5Hour30Minute'
since
I don't think JET will recognize these as durations!

--
HTH
Van T. Dinh
MVP (Access)




Shell said:
John:
I have tried as you instructed. I inserted the following code into a
new
query: Insert Into [Total Record]
(EmployeeID,DeptID,StartDate,EndDate,OutTime,TotalOutTime,LeaveTime,TotalLeaveTime,OTTime,TotalOTime,LateTime,EarlyLeaveTime,LackTime,Note)
Values
('0003','06','#1/1/2000#','#1/1/2007#','2','184d','2','14Day','1','5Hour30Minute','4','0','1622','OK')
It shows the same problem and also emphasize on "Note" field. "Note"
field
is a text field. I don't know what's wrong with this field.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

According to http://www.allenbrowne.com/AppIssueBadWord.html, Note is a
reserved word.

If you cannot (or will not) rename the field, at least put square brackets
around it: [Note]



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shell said:
I've tried as you instructed. But the result is the same. (EmployeeID and
DeptID are also text fields)

Douglas J. Steele said:
What happens if you put in spaces between the table name and the list of
fields, and between the list of fields and the keyword VALUES?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shell said:
Van:
Thanks for your response!
I have done like you instructed me as following:
INSERT INTO [Total
Record](EmployeeID,DeptID,StartDate,EndDate,OutTime,TotalOutTime,LeaveTime,TotalLeaveTime,OTTime,TotalOTime,LateTime,EarlyLeaveTime,LackTime,Note)VALUES
( '0003
','06',#1/1/2000#,#1/1/2007#,2,'184d',2,'14Day',1,'5Hour30Minute',4,0,1622,'')
I have set "TotalOutTime" field, "TotalLeaveTime" field and
"TotalOTime"
field as text field. But the result is the same:Syntax error in Insert
Into
statement.

:

Don't enlcose every explicit value in single quotes. Use the
following:

1. Text value: delimited by single-quotes or double quotes.

2. DateTime value: delimited by hashes (#) and the date in US format
"mm/dd/yyyy hh:nn:ss"
or an internationally unambiguous format like "yyyy-mm-dd HH:nn:ss".

3. Numeric value: no delimiters use.

I think you construction should result to something like:

====
INSERT INTO [Total Record]
(EmployeeID, DeptID, StartDate, EndDate, OutTime, TotalOutTime,
LeaveTime, TotalLeaveTime, OTTime, TotalOTime, LateTime,
EarlyLeaveTime, LackTime, Note)
Values
(3, 6, #1/1/2000#, #1/1/2007#, 2, ???,
2, ???, 1, ???,
4, 0, 1622, 'OK')
====

I don't know what you want to do with '184d', '14Day', '5Hour30Minute'
since
I don't think JET will recognize these as durations!

--
HTH
Van T. Dinh
MVP (Access)




John:
I have tried as you instructed. I inserted the following code into a
new
query: Insert Into [Total Record]
(EmployeeID,DeptID,StartDate,EndDate,OutTime,TotalOutTime,LeaveTime,TotalLeaveTime,OTTime,TotalOTime,LateTime,EarlyLeaveTime,LackTime,Note)
Values
('0003','06','#1/1/2000#','#1/1/2007#','2','184d','2','14Day','1','5Hour30Minute','4','0','1622','OK')
It shows the same problem and also emphasize on "Note" field. "Note"
field
is a text field. I don't know what's wrong with this field.
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

INSERT INTO [Total
Record](EmployeeID,DeptID,StartDate,EndDate,OutTime,TotalOutTime?LeaveTime?TotalLeaveTime,OTTime,TotalOTime,LateTime,EarlyLeaveTime,LackTime,Note)VALUES
Click to expand...

Is this a copy and paste of the actual SQL? If so, the question marks
in

TotalOutTime?LeaveTime?TotalLeaveTime

are almost surely invalid. Is this three fields which should have
commas?

John W. Vinson[MVP]
 
G

Guest

Well done! Thanks a lot!
Shell Hu

Douglas J. Steele said:
What happens if you put in spaces between the table name and the list of
fields, and between the list of fields and the keyword VALUES?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Shell said:
Van:
Thanks for your response!
I have done like you instructed me as following:
INSERT INTO [Total
Record](EmployeeID,DeptID,StartDate,EndDate,OutTime,TotalOutTime,LeaveTime,TotalLeaveTime,OTTime,TotalOTime,LateTime,EarlyLeaveTime,LackTime,Note)VALUES
( '0003
','06',#1/1/2000#,#1/1/2007#,2,'184d',2,'14Day',1,'5Hour30Minute',4,0,1622,'')
I have set "TotalOutTime" field, "TotalLeaveTime" field and "TotalOTime"
field as text field. But the result is the same:Syntax error in Insert
Into
statement.

Van T. Dinh said:
Don't enlcose every explicit value in single quotes. Use the following:

1. Text value: delimited by single-quotes or double quotes.

2. DateTime value: delimited by hashes (#) and the date in US format
"mm/dd/yyyy hh:nn:ss"
or an internationally unambiguous format like "yyyy-mm-dd HH:nn:ss".

3. Numeric value: no delimiters use.

I think you construction should result to something like:

====
INSERT INTO [Total Record]
(EmployeeID, DeptID, StartDate, EndDate, OutTime, TotalOutTime,
LeaveTime, TotalLeaveTime, OTTime, TotalOTime, LateTime,
EarlyLeaveTime, LackTime, Note)
Values
(3, 6, #1/1/2000#, #1/1/2007#, 2, ???,
2, ???, 1, ???,
4, 0, 1622, 'OK')
====

I don't know what you want to do with '184d', '14Day', '5Hour30Minute'
since
I don't think JET will recognize these as durations!

--
HTH
Van T. Dinh
MVP (Access)




John:
I have tried as you instructed. I inserted the following code into a
new
query: Insert Into [Total Record]
(EmployeeID,DeptID,StartDate,EndDate,OutTime,TotalOutTime,LeaveTime,TotalLeaveTime,OTTime,TotalOTime,LateTime,EarlyLeaveTime,LackTime,Note)
Values
('0003','06','#1/1/2000#','#1/1/2007#','2','184d','2','14Day','1','5Hour30Minute','4','0','1622','OK')
It shows the same problem and also emphasize on "Note" field. "Note"
field
is a text field. I don't know what's wrong with this field.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

syntax error apostrophe in criteria 3
Syntax Error - INSERT INTO Statement 4
Dataadapter produces error: Syntax error in INSERT INTO statement 1

Top