3134 Syntax error in INSERT INTO statement 1

[ajaeger]

I'm getting the error "3134 Syntax error in INSERT INTO statement" when I try to execute the following code:

sql = "Insert into dbrder_Lines (ORDER_NUMBER,LINE_NUMBER,PRODUCT_CODE,DESCRIPTION,QUANTITY_ORDERED,QUANTITY_SHIPPED,UNIT_PRICE,
UNDISCOUNTED_PRICE,EXTENDED_AMOUNT,UNDISCOUNTED_EXTENDED_AMOUNT, NOTE) "
sql = sql & "values ('" & OrderNum & "','" & LineNum & "','" & Product & "','" & ProductDesc & "',1,1," & ProductPrice & "," & ProductPrice & "," & ProductPrice & "," & ProductPrice & ",'" & rsAttendees!Grade & "')"

db.Execute (sql)


The actual sql that is generated by the code is:
Insert into dbrder_Lines
(ORDER_NUMBER,LINE_NUMBER,PRODUCT_CODE,DESCRIPTION,QUANTITY_ORDERED,QUANTITY_SHIPPED,UNIT_PRICE,
UNDISCOUNTED_PRICE,EXTENDED_AMOUNT,UNDISCOUNTED_EXTENDED_AMOUNT,NOTE)
values ('2987388','2','2009NLC/GRADE','Grade - Enter 5-12 Under "Function Note" tab',1,1,0,0,0,0,NOTE)

It will run if I delete the last field, the NOTE field where I'm assigning the value of '11' to it (in blue above). So it's something with that last field...


Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[HarleyQuinn]

Assuming that NOTE is a text field the first thing I would notice is that NOTE is a reserved word in Access, might be worth changing the field name to something else as reserved words are better not used as field names.

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[ajaeger]

Note is a memo field, but I can't change the name of it since it is in another SQL database. Is there anyway to work around this?

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[HarleyQuinn]

Have you tried:

Insert into dbo_Order_Lines
(ORDER_NUMBER,LINE_NUMBER,PRODUCT_CODE,DESCRIPTION,QUANTITY_ORDERED,QUANTITY_SHIPPED,UNIT_PRICE,
UNDISCOUNTED_PRICE,EXTENDED_AMOUNT,UNDISCOUNTED_EXTENDED_AMOUNT,[NOTE])

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[ajaeger]

I was just thinking that same thought. Thanks HarleyQuinn, that worked!

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com