Syntax error in Insert Into statement

[Guest]

I have an insert statement I want to run when a button on a form is clicked.
If 4/5 of the fields are completed on the form the statement will run fine
but if all 5 are completed, I get the above error message. I have checked and
checked the statement and cannot find why this is happening.

Anyone got any ideas? Help would be much appreciated.

BDownStmt = "INSERT INTO Tbl_Breakdown
([BDown_Date],[BDown_RouteNo],[BDown_Start],[BDown_Fin],[BDown_Veh])" & _
"VALUES ('#" & Me.Route_Date & "#','" & Me.Route_Num & "','#" &
Me.BDStart & "#','#" & Me.BDFin & "#','" & Me.BDVeh & "')"

Thank You

 

[Guest]

There is no need to put a single quote before and after a date and number
fields, you need to put it when text field involved

So if you have a date Field it should be
",#" & FieldName & "#,"

Number
"," & FieldName & ","

String
",'" & FieldName & "',"

So check the field Types and remove the single quotes from the sql string
Start with the date fields

 

[Guest]

Thanks for the reply, I've done as you said but still get the same problem.

Any other thoughts?

There is no need to put a single quote before and after a date and number
fields, you need to put it when text field involved

So if you have a date Field it should be
",#" & FieldName & "#,"

Number
"," & FieldName & ","

String
",'" & FieldName & "',"

So check the field Types and remove the single quotes from the sql string
Start with the date fields


--
In God We Trust - Everything Else We Test

I have an insert statement I want to run when a button on a form is clicked.
If 4/5 of the fields are completed on the form the statement will run fine
but if all 5 are completed, I get the above error message. I have checked and
checked the statement and cannot find why this is happening.

Anyone got any ideas? Help would be much appreciated.

BDownStmt = "INSERT INTO Tbl_Breakdown
([BDown_Date],[BDown_RouteNo],[BDown_Start],[BDown_Fin],[BDown_Veh])" & _
"VALUES ('#" & Me.Route_Date & "#','" & Me.Route_Num & "','#" &
Me.BDStart & "#','#" & Me.BDFin & "#','" & Me.BDVeh & "')"

Thank You

 

[Guest]

Has to be a space before VALUES:

([BDown_Date],[BDown_RouteNo],[BDown_Start],[BDown_Fin],[BDown_Veh])" & _
" VALUES ('#" & Me.Route_Date & "#','" & Me.Route_Num & "','#" &
^ Add a space here or
here ^

--
Chaim

Thanks for the reply, I've done as you said but still get the same problem.

Any other thoughts?

There is no need to put a single quote before and after a date and number
fields, you need to put it when text field involved

So if you have a date Field it should be
",#" & FieldName & "#,"

Number
"," & FieldName & ","

String
",'" & FieldName & "',"

So check the field Types and remove the single quotes from the sql string
Start with the date fields


--
In God We Trust - Everything Else We Test

I have an insert statement I want to run when a button on a form is clicked.
If 4/5 of the fields are completed on the form the statement will run fine
but if all 5 are completed, I get the above error message. I have checked and
checked the statement and cannot find why this is happening.

Anyone got any ideas? Help would be much appreciated.

BDownStmt = "INSERT INTO Tbl_Breakdown
([BDown_Date],[BDown_RouteNo],[BDown_Start],[BDown_Fin],[BDown_Veh])" & _
"VALUES ('#" & Me.Route_Date & "#','" & Me.Route_Num & "','#" &
Me.BDStart & "#','#" & Me.BDFin & "#','" & Me.BDVeh & "')"

Thank You

 

[Guest]

Can you post the new SQL, and the fields type.
Does the date fields always contain value when you run the sql?

--
In God We Trust - Everything Else We Test

Thanks for the reply, I've done as you said but still get the same problem.

Any other thoughts?

There is no need to put a single quote before and after a date and number
fields, you need to put it when text field involved

So if you have a date Field it should be
",#" & FieldName & "#,"

Number
"," & FieldName & ","

String
",'" & FieldName & "',"

So check the field Types and remove the single quotes from the sql string
Start with the date fields


--
In God We Trust - Everything Else We Test

I have an insert statement I want to run when a button on a form is clicked.
If 4/5 of the fields are completed on the form the statement will run fine
but if all 5 are completed, I get the above error message. I have checked and
checked the statement and cannot find why this is happening.

Anyone got any ideas? Help would be much appreciated.

BDownStmt = "INSERT INTO Tbl_Breakdown
([BDown_Date],[BDown_RouteNo],[BDown_Start],[BDown_Fin],[BDown_Veh])" & _
"VALUES ('#" & Me.Route_Date & "#','" & Me.Route_Num & "','#" &
Me.BDStart & "#','#" & Me.BDFin & "#','" & Me.BDVeh & "')"

Thank You

 

[Guest]

In my reader, that last post didn't come out as I had hoped. It should show a
space either after the ')' closing the field list or before the VALUES
keyword.
--
Chaim

Has to be a space before VALUES:

([BDown_Date],[BDown_RouteNo],[BDown_Start],[BDown_Fin],[BDown_Veh])" & _
" VALUES ('#" & Me.Route_Date & "#','" & Me.Route_Num & "','#" &
^ Add a space here or
here ^

--
Chaim

Thanks for the reply, I've done as you said but still get the same problem.

Any other thoughts?

There is no need to put a single quote before and after a date and number
fields, you need to put it when text field involved

So if you have a date Field it should be
",#" & FieldName & "#,"

Number
"," & FieldName & ","

String
",'" & FieldName & "',"

So check the field Types and remove the single quotes from the sql string
Start with the date fields


--
In God We Trust - Everything Else We Test


:

I have an insert statement I want to run when a button on a form is clicked.
If 4/5 of the fields are completed on the form the statement will run fine
but if all 5 are completed, I get the above error message. I have checked and
checked the statement and cannot find why this is happening.

Anyone got any ideas? Help would be much appreciated.

BDownStmt = "INSERT INTO Tbl_Breakdown
([BDown_Date],[BDown_RouteNo],[BDown_Start],[BDown_Fin],[BDown_Veh])" & _
"VALUES ('#" & Me.Route_Date & "#','" & Me.Route_Num & "','#" &
Me.BDStart & "#','#" & Me.BDFin & "#','" & Me.BDVeh & "')"

Thank You

 

[John Vinson]

I have an insert statement I want to run when a button on a form is clicked.
If 4/5 of the fields are completed on the form the statement will run fine
but if all 5 are completed, I get the above error message. I have checked and
checked the statement and cannot find why this is happening.

Anyone got any ideas? Help would be much appreciated.

BDownStmt = "INSERT INTO Tbl_Breakdown
([BDown_Date],[BDown_RouteNo],[BDown_Start],[BDown_Fin],[BDown_Veh])" & _
"VALUES ('#" & Me.Route_Date & "#','" & Me.Route_Num & "','#" &
Me.BDStart & "#','#" & Me.BDFin & "#','" & Me.BDVeh & "')"

Thank You

I think part of the problem is that you're using belt and braces - or,
in this case, singlequotes AND octothorpes. You need only a #
octothorpe for date fields, and only a ' for text fields, and no
delimiter at all for numbers; you're using both for your datefields.

Try

BDownStmt = "INSERT INTO Tbl_Breakdown
([BDown_Date],[BDown_RouteNo],[BDown_Start], _
[BDown_Fin],[BDown_Veh])" & _
"VALUES (#" & Me.Route_Date & "#," & Me.Route_Num & ",#" &
Me.BDStart & "#,#" & Me.BDFin & "#,'" & Me.BDVeh & "')"

assuming that Route_Num is in fact a Number type field.

John W. Vinson[MVP]