strange query error

[Joseph S.]

Hi all,
the following query does not work :
(I am using a generic database access tool to create tables and enter
data into an mdb file)

CREATE TABLE Customers
(
CustID autoincrement(10,10) not null ,
CompanyName text(75) NULL,
ContactName text(75) NULL,
CustType text(10) NULL,
Area text(50) NULL,
Address text(255) NULL,
City VARCHAR(50) NULL,
State VARCHAR(50) NULL,
PINCode VARCHAR(10) NULL,
Phone VARCHAR(50) NULL,
Mobile VARCHAR(50) NULL,
Fax VARCHAR(50) NULL,
Email VARCHAR(80) NULL,
CurrCredit DECIMAL(18,2) NULL,
primary key (CustID)
);

The error from Access 2000 is:
An error occurred when executing the SQL command:
CREATE TABLE Customers
(
CustID autoincrement(10,10) not null ,
CompanyName text(75) NULL,
ContactName text(75) NULL,
CustT...

[Microsoft][ODBC Microsoft Access Driver] Syntax error in field
definition. [SQL State=37000, DB Errorcode=-3553]

Total execution time: 0.0s
(Statement 22 of 22 finished.)

I thought maybe Area is a keyword for Access 2000, so I changed the
column name to MyArea - but same result.

CustType surely could not be a keyword.

Then I tried making all text fields as text(N), no luck
Then all text fields as Varchar(N), still no luck.
Finally, deleted the column MyArea, still no luck.

BTW, both text(N) and Varchar(N) work perfectly fine - I tried that out
with another table.

Any clues what is wrong?

TIA,
JS

 

[OfficeDev18 via AccessMonster.com]

Joseph,

When I threw the SQL into Access' query designer, I got the error message on
this line:

CurrCredit DECIMAL(18,2) NULL,

with the word DECIMAL selected. I don't know why, but maybe you can figure
out the problem.

Sam

Hi all,
the following query does not work :
(I am using a generic database access tool to create tables and enter
data into an mdb file)

CREATE TABLE Customers
(
CustID autoincrement(10,10) not null ,
CompanyName text(75) NULL,
ContactName text(75) NULL,
CustType text(10) NULL,
Area text(50) NULL,
Address text(255) NULL,
City VARCHAR(50) NULL,
State VARCHAR(50) NULL,
PINCode VARCHAR(10) NULL,
Phone VARCHAR(50) NULL,
Mobile VARCHAR(50) NULL,
Fax VARCHAR(50) NULL,
Email VARCHAR(80) NULL,
CurrCredit DECIMAL(18,2) NULL,
primary key (CustID)
);

The error from Access 2000 is:
An error occurred when executing the SQL command:
CREATE TABLE Customers
(
CustID autoincrement(10,10) not null ,
CompanyName text(75) NULL,
ContactName text(75) NULL,
CustT...

[Microsoft][ODBC Microsoft Access Driver] Syntax error in field
definition. [SQL State=37000, DB Errorcode=-3553]

Total execution time: 0.0s
(Statement 22 of 22 finished.)

I thought maybe Area is a keyword for Access 2000, so I changed the
column name to MyArea - but same result.

CustType surely could not be a keyword.

Then I tried making all text fields as text(N), no luck
Then all text fields as Varchar(N), still no luck.
Finally, deleted the column MyArea, still no luck.

BTW, both text(N) and Varchar(N) work perfectly fine - I tried that out
with another table.

Any clues what is wrong?

TIA,
JS

 

[Joseph S.]

Joseph,

When I threw the SQL into Access' query designer, I got the error message on
this line:

CurrCredit DECIMAL(18,2) NULL,

with the word DECIMAL selected. I don't know why, but maybe you can figure
out the problem.

Hi, thanks for the reply.

That got me thinking in the right direction - if a keyword shows an
error, look up the error _and_ the keyword in usenet/MSDN

and this is what i found:
firstly,
from the microsoft.com page on access 2000:
http://support.microsoft.com/default.aspx?scid=kb;en-us;275561
"Supported synonyms are Dec and Numeric"
numeric worked for me.
It also has a lot of info on create alter table, user, group etc.

Then these two posts for decimal in a query:
http://groups.google.com/group/micr...b849bd91f3?q=decimal(&rnum=9#6db507b849bd91f3

and

http://groups.google.com/group/micr...6925ae16ef?q=decimal(&rnum=6#8f3da86925ae16ef

Problem more or less solved.

Thanks,
JS