Hi all,

I'm trying to do an insert into query which worked until i added a variable.
The red part used to be a direct reference to the form (me.combo.column(0), which worked), but i have since changed it to a variable which im now getting an error : syntax error in insert into statement


'Create NEW charge transaction
sql = "INSERT INTO TRANSACTIONS (reqNo, Company, charge, hours, quantity, reqDt) " & _
"VALUES ('" & Me.txtJobNo.Value & "', " & _
"'" & Me.comboComp.Column(0) & "', " & _
"'" & val & "', " & _
"'" & Me.comboHrs.Column(0) & "', " & _
"" & Me.comboQty.Column(0) & ", " & _
"'" & dt & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL (sql)

can anyone see what i'm doing wrong??


Thanks,
Spinkung.

Are you getting an Error message. If so what? You might find it useful to have a debug.print sql just before the docmd statement. That will let you see exactly what you are passing as SQL.

val is the name of a VBA function to convert a string to a double, change the name of your variable.
Hi all,

I'm trying to do an insert into query which worked until i added a variable.
The red part used to be a direct reference to the form (me.combo.column(0), which worked), but i have since changed it to a variable which im now getting an error : syntax error in insert into statement


'Create NEW charge transaction
sql = "INSERT INTO TRANSACTIONS (reqNo, Company, charge, hours, quantity, reqDt) " & _
"VALUES ('" & Me.txtJobNo.Value & "', " & _
"'" & Me.comboComp.Column(0) & "', " & _
"'" & val & "', " & _
"'" & Me.comboHrs.Column(0) & "', " & _
"" & Me.comboQty.Column(0) & ", " & _
"'" & dt & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL (sql)

can anyone see what i'm doing wrong??


Thanks,
Spinkung.

here is the result of my debug.....

INSERT INTO TRANSACTIONS (reqNo, Company, charge, hours, quantity, reqDt) VALUES ('ref', 'AUD', '14.50', '', , '11/09/2007')

the red part is my variable which is now called vl

the error is: syntax error in INSERT INTO statement

here is the result of my debug.....



the red part is my variable which is now called vl

the error is: syntax error in INSERT INTO statement

Looks like you have an unpaired double quote after the hours value.

Check the data type of the charge field. Are you trying to insert '14.50' as text when you should have a numeric value. If so remove the '' from round vl.
'hours' appears to be text and 'qty' numeric; check thats right, and also check whether they are required and/or zero length is allowed in your table, as you're trying to insert a zero length string into 'hours' and you're leaving 'qty' null.