The word "Note" causes error in SQL INSERT INTO statement.

Question

I have an SQL insert statement:

INSERT INTO Customers (FirstName, LastName, Note) VALUES ('Sam', 'Jones', 'Works Mondays Only');

If I run the above statement as is, Access generates this error message:

   Syntax error in INSERT INTO statement.

But if I put brackets around [Note] it works.

IF I change the name to fNote without brackets.....It works. (could be jNote or anything.)

There are no other fields in entire database named 'Note'.

The word 'Note' is a short text data type set in table.

I even created a new database with only one table with the word "Note" as a field and same thing. Error if I try to use this word without brackets.

HELP.....!!!!!! AM I MISSING SOMETHING.

The word "Note" isn't a reserved word as far as I can find.

Whats wrong with the word "Note" ?????

Answer 1

Hi

As Danial mentioned - reserved words are a NO-NO in SQL.

try , ,

INSERT INTO Customers (FirstName, LastName, [Note]) VALUES ('Sam', 'Jones', 'Works Mondays Only');

the [ and ] denote that the contents are a field name.

Better yet - give all your fieldnames a prefix - then you can have unique non-reserved names.

Use a different prefix for each table

eg for tbl_CUSTOMERS use CUST_xxxxxxx

and for tbl_EMPLOYEES . .  maybe EMP_xxxxxx

hope that helps

cheers

PaulG

Answer 2

This is most probably a case of Reserved Word conflict.  If you check out the Reserved Word listing found at:  http://allenbrowne.com/AppIssueBadWord.html#N you'll see that Note is listed as a Reserved word and should not be used when naming objects.

---

Daniel Pineault - 2010-2013 MVP

http://www.cardaconsultants.com

http://www.devhut.net