insert into with vba

[spinkung]

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) & "', " & _
      [COLOR="Red"]"'" & val & "', " & _[/COLOR]      
      "'" & 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.

 

[Rabbie]

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.

 

[DJkarl]

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) & "', " & _
      [COLOR="Red"]"'" & val & "', " & _[/COLOR]      
      "'" & 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.

 

[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

 

[Rabbie]

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.

 

[Bungle]

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.