Inserting value in a column named 'Date'

[murtzkb]

I cannot insert a value in a column whose design name is "Date". Even if I use a qualifier like tablename.Date I get an error saying column with this name does not exist. However the same qualifier works for an update statement.

 

[JarlH]

Do you really mean 'UPDATE tab SET Date = nnnnn' works, but 'INSERT INTO tab (Date) VALUES (nnnnn)' doesn't? Then your dbms has a bug.

Besides it's a bad idea naming a column Date, because DATE is a reserved word in SQL. That means you have to double quote that name every time specifying it.

Like:
SELECT "Date", another_column FROM some_table

UPDATE some_table SET "Date" = nnnnn

INSERT INTO some_table ("Date", another_column) VALUES (nnnnn, yyyyy)

etc.

 

[murtzkb]

Sorry..... Your suggested idea of using quotes for the column name date does not work.... Do u have another sloution.....

 

[JarlH]

Have you tried any possible combination of upper and lower case?

Like "DATE", "date", "Date" etc?

Double quoting identifiers is the ANSI/ISO SQL way. What database product are you using? Maybe you should try a vendor specific forum.

 

[murtzkb]

I am querying a simple MS Access Database table. I have now tried various possible combinations with quotes but i always get an error saying "Column does not exist" and if i remove the quotes then a syntax error for using name aready used by Access

 

[r937]

microsoft access? you shoulda said so in the first place

use [Date]

rudy

http://r937.com/

SQL Consulting