5

I have a query that by all rights should not possibly fail, and I can't for the life of me figure out why

INSERT INTO Grocery_Store_Prices(Store,Item,Brand,Price,Unit,Quantity,Note) 
VALUES("Kroger","Cheesy Poof","Cartman",0.51,"fart",15,"what is going on");

When I try to run the query I get "Syntax error in INSERT INTO statement" with the Note field highlighted. If I omit the Note field and its value, the query works fine. Is there something really obvious I'm missing, or is there an Jet SQL quirk buried here???

The table it's acting on is: Grocery_Store_Prices

  • ID -- autonumber primary key
  • Store -- Text
  • Date -- Date/Time
  • Item -- Text
  • Brand -- Text
  • Price -- Currency
  • Unit -- Text
  • Quantity -- Number (double)
  • Note -- Text.
IAmTimCorey
  • 16,412
  • 5
  • 39
  • 75
BobMcGee
  • 19,824
  • 10
  • 45
  • 57

2 Answers2

9

"Note" is a reserved word in Microsoft Access. You need to surround it with square brackets:

INSERT INTO Grocery_Store_Prices(Store,Item,Brand,Price,Unit,Quantity,[Note])
VALUES("Kroger","Cheesy Poof","Cartman",0.51,"fart",15,"what the ____");

Helpful list of reserved words here: http://support.microsoft.com/kb/286335

Some consider it best practice to always encase field names in square brackets, just so you don't have to worry about it.

Good luck!

Michael Ames
  • 2,607
  • 1
  • 16
  • 22
  • That'll do it. Ugh, Jet SQL... guess I've gotten spoiled by using DBMSes with richer syntax and smarter handling. – BobMcGee Jun 03 '11 at 16:33
  • 2
    Reserved words are a problem in EVERY database. The issue is how the database interfaces handle it for you. Use of reserved words is discouraged in ALL databases. It would, I guess, be nice if Access told you about the reserved word, though. – David-W-Fenton Jun 09 '11 at 01:51
  • @David-W-Fenton: reserved words are not a problem in my databases, probably because I follow the spirit of [ISO 11179](http://en.wikipedia.org/wiki/ISO/IEC_11179) data element naming conventions. Or are you, I wonder, saying "database" when you actually mean [DBMS](http://en.wikipedia.org/wiki/Database_management_system)? – onedaywhen Jun 15 '11 at 08:38
4

Note is a reserved word, so try renaming that column.

paulmorriss
  • 2,579
  • 25
  • 30
  • ... and, of course that fixed it. Knew it was something simple. Figures that Access is dumb enough to allow reserved words in naming fields without prompting. – BobMcGee Jun 03 '11 at 16:19
  • 2
    Access is much too accommodating about unwise object name choices, IMO. It will definitely allow the unwary developer to shoot himself in the foot. – HansUp Jun 03 '11 at 16:49
  • 1
    @HansUp--indeed. This is my biggest complaint with Access. Becuse of it, I always dread being asked to "inherit" an Access project unless I know & trust the developer ahead of me. Amateurs seem to always stumble upon the reserved words, or--worse, IMHO--put spaces in their field names. – RolandTumble Jun 03 '11 at 18:45