Syntax error in SQL INSERT INTO statement 1

[BrianLe]

In Access 97, I added the ... & "#" tbx3.Value & "#," _ ... to the list in the following INSERT INTO statement and now I get a syntax error. Can anyone find it?

SQL = "INSERT INTO tblSchedule (ScheduleDate,TimeStamp,TrainNo,TrainPart,DateTypeID,DateID) " & _
                 "VALUES(#" & tbx1.Value & "#," _
                            & "#" tbx3.Value & "#," _
                            & DQ & lbx1.Column(0) & DQ & "," _
                            & DQ & lbx2.Column(0, itm) & DQ & "," _
                            & DQ & cbx1.Column(0) & DQ & "," _
                            & DQ & cbx2.Column(0) & DQ & ");"

Thanks, Brian

 

[PHV]

& "#" [!]&[/!] tbx3.Value & "#," _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

I still get the error with the added &. TimeStamp is formatted Date/Time.

 

[PHV]

What is the value of SQL at the time the error raise ?
BTW, it's a VBA or SQL error message ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

How do I determine the "value of SQL"?

Thanks, Brian

 

[PHV]

Debug.Print SQL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

Do I put it after my list? Where do I see the 'print'?

Thanks, Brian

 

[PHV]

You put the print after the assignment of SQL but before you execute it.
The result'll display in the debug window (Ctrl+G)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

Here is the current code.

SQL = "INSERT INTO tblSchedule (ScheduleDate,TimeStamp,TrainNo,TrainPart,DateTypeID,DateID) " & _
                 "VALUES(#" & tbx1.Value & "#," _
                            & "# & tbx3.Value & #," _
                            & DQ & lbx1.Column(0) & DQ & "," _
                            & DQ & lbx2.Column(0, itm) & DQ & "," _
                            & DQ & cbx1.Column(0) & DQ & "," _
                            & DQ & cbx2.Column(0) & DQ & ");"

and the SQL value
INSERT INTO tblSchedule (ScheduleDate,TimeStamp,TrainNo,TrainPart,DateTypeID,DateID) VALUES(#7/19/07 2:00:00 AM#,# & tbx3.Value & #,"2007PAT1","B","2","1");

Thanks, Brian

 

[BrianLe]

I put the code back to this.

SQL = "INSERT INTO tblSchedule (ScheduleDate,TimeStamp,TrainNo,TrainPart,DateTypeID,DateID) " & _
                 "VALUES(#" & tbx1.Value & "#," _
                            & "#" & tbx3.Value & "#," _
                            & DQ & lbx1.Column(0) & DQ & "," _
                            & DQ & lbx2.Column(0, itm) & DQ & "," _
                            & DQ & cbx1.Column(0) & DQ & "," _
                            & DQ & cbx2.Column(0) & DQ & ");"

Now the SQL value is

INSERT INTO tblSchedule (ScheduleDate,TimeStamp,TrainNo,TrainPart,DateTypeID,DateID) VALUES(#7/24/07 2:00:00 AM#,#8/4/07 10:56:08 AM#,"2007PAT1","C","1","1");

Still getting the syntax error.

Thanks, Brian
Thanks, Brian

 

[PHV]

Are both DateTypeID and DateID defined as text in tblSchedule ?

Which line is highlighted when in debug mode at the time the error raise ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

DateTypeID and DateID are numbers. Unfortunately, I'm away from my project so I can't see what line is highlighted.

Thanks, Brian

 

[PHV]

DateTypeID and DateID are numbers
So, you shouldn't quote their value:

SQL = "INSERT INTO tblSchedule (ScheduleDate,TimeStamp,TrainNo,TrainPart,DateTypeID,DateID) " & _
                 "VALUES(#" & tbx1.Value & "#," _
                      & "#" & tbx3.Value & "#," _
                       & DQ & lbx1.Column(0) & DQ & "," _
                       & DQ & lbx2.Column(0, itm) & DQ & "," _
                            & cbx1.Column(0) & "," _
                            & cbx2.Column(0) & ");"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

I'll give it a try when I get home tonight or tomorrow morning. Can you tell me how I would add a line for txb2 that is formatted as text?

Thanks, Brian

 

[BrianLe]

I'm making progress. This is what I have now.

SQL = "INSERT INTO tblSchedule (ScheduleDate,TStamp,Notes,TrainNo,TrainPart,DateTypeID,DateID) " & _
                 "VALUES(#" & tbx1.Value & "#," _
                            & "#" & tbx3.Value & "#," _
                                  & tbx2.Text & "," _
                            & DQ & lbx1.Column(0) & DQ & "," _
                            & DQ & lbx2.Column(0, itm) & DQ & "," _
                                & cbx1.Column(0) & "," _
                                & cbx2.Column(0) & ");"
[\code]

It worked OK until I tried to add the line to put in the "Notes" ..tbx2.Text ...  Is that line correct?

Now I get error message "You can't reference a property or method for a control unless the control has the focus".

Thanks, Brian

 

[PHV]

I suppose that notes is defined as Text in tblSchedule, so you have to use quoting:

SQL = "INSERT INTO tblSchedule (ScheduleDate,TStamp,Notes,TrainNo,TrainPart,DateTypeID,DateID) " & _
                 "VALUES(#" & tbx1.Value & "#," _
                      & "#" & tbx3.Value & "#," _
                       [!]& DQ[/!] & tbx2.[!]Value & DQ[/!] & "," _
                       & DQ & lbx1.Column(0) & DQ & "," _
                       & DQ & lbx2.Column(0, itm) & DQ & "," _
                            & cbx1.Column(0) & "," _
                            & cbx2.Column(0) & ");"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

The insert works great.

Thank you very much, Brian