sql syntax error with insert into

[kaosyeti via AccessMonster.com]

this is the code that's giving me a syntax error:

strSql = "insert into tblnewoptions (Option, OptionCode, AllocGroup) values
('" & Me.txtboxOption & "'" & ", '" & Me.txtboxOptionCode & "'" & ", '" & Me.
txtboxAllocGroup & "');"
Debug.Print strSql
CurrentDb.Execute strSql

and here is the debug.print line:

insert into tblnewoptions (Option, OptionCode, AllocGroup) values('test desc',
'test code', 'test alloc');

the three test values are being pulled from text boxes on a form. this code
is in a button on that form.

hope this is an easy one. any ideas? thanks.

greg

 

[Guest]

Greg,

The syntax problem in your statement is related to the word Option. This is
not a great field name since it is a Keyword. If you must use it you can
simply enclose it in square brackets as in:

insert into tblnewoptions ([Option], OptionCode, AllocGroup) values('test
desc',
'test code', 'test alloc');

I tested this and it worked fine in Access 2000.

HTH,

Dave Smith

 

[kaosyeti via AccessMonster.com]

that fixed it. i knew enough to stay away from 'date' and 'year' and a
handful of others, but never thought that 'option' was a keyword. thanks!

Greg,

The syntax problem in your statement is related to the word Option. This is
not a great field name since it is a Keyword. If you must use it you can
simply enclose it in square brackets as in:

insert into tblnewoptions ([Option], OptionCode, AllocGroup) values('test
desc',
'test code', 'test alloc');

I tested this and it worked fine in Access 2000.

HTH,

Dave Smith

this is the code that's giving me a syntax error:

insert into tblnewoptions (Option, OptionCode, AllocGroup) values('test desc',
'test code', 'test alloc');