SQL numeric Error

Question

I have this problem when using Microsoft Access on SQL view I always get syntax error but when I remove the numeric line it runs ok !!

How can I fix that?

create table student
    (ID varchar (5),
    name varchar (20) not null,
    dept_name varchar (20),
    tot_cred numeric (3,0) default 0,
    primary key (ID))

This is the error I get

The command runs as is against SQL Server 2008 R2, via SQL Server Management Studio. So probably specific to the MS Access to SQL Server. — Shannon Severance, Nov 24 '15 at 18:06
It might be the numeric type. I'm not sure what the Access equivalent is. Try `decimal` instead of `numeric`. — shawnt00, Nov 24 '15 at 18:07
Take a look at https://msdn.microsoft.com/en-us/library/ms714540%28v=vs.85%29.aspx — Little Santi, Nov 24 '15 at 18:08
It is the numeric type because if I remove it , it run all fine ,, I tried int and decimal give me the same error — Mayyasah Al-Bahrani, Nov 24 '15 at 18:09
Try just `numeric` without the `(3, 0)`. Nevermind--that MSDN page says "NUMERIC - Data type limited to Microsoft Access 4.0." — shawnt00, Nov 24 '15 at 18:10
.. for the primary key there is no issue with that ,, only the numeric — Mayyasah Al-Bahrani, Nov 24 '15 at 18:11
If you want an int then you'll have to call it `number` according to that same doc. — shawnt00, Nov 24 '15 at 18:13
Sql Server is another DBMS. It's basically a different Access. — Julien Blanchard, Nov 24 '15 at 18:19

Julien Blanchard · Answer 1 · 2015-11-24T18:24:36.373

1

According to Access' documentation, Number is to be used, not Numeric.

The reason that it cannot switch out of SQL is that it tries to fit the SQL you wrote into the Wizard, but he cannot match numeric(3,0) to a Datatype. the easiest way to solve that issue is to remove the field, and create it against from the wizard by using the Datatype dropdown.

So your SQL should look a bit like this :

create table student
(ID varchar (5),
name varchar (20) not null,
dept_name varchar (20),
tot_cred number default 0,
primary key (ID))

I'm not certain how you add the default clause, but according to their syntax, it cant, be done in the CREATE TABLE statement. I also believe it depends on your version of Access (hopefully 2000+ because otherwise you'll have to use workarounds).

Once again, the easiest way to do it is to use the Wizard and look at the result in the SQL View.

edited Nov 24 '15 at 18:24

answered Nov 24 '15 at 18:19

Julien Blanchard

825
5
18

It gave me an error on the word " default " ,, I have 2010 version – Mayyasah Al-Bahrani Nov 24 '15 at 18:34
Then, like I said, remove `default 0`, hop into the "Wizard View" (aka editor mode), add your default value, and hop back into "SQL View". You will know the correct syntax. – Julien Blanchard Nov 24 '15 at 18:36
1

There is no way to set the `default` value in the query design editor either. – Andre Nov 24 '15 at 19:31

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

You cannot use the DEFAULT clause with a CREATE TABLE statement in Access SQL, at least not when running it from the query editor / SQL view.

You need to either run the SQL command via an ADO connection:
SQL SET DEFAULT not working in MS Access

or set the Access option under Query Design to ANSI 92 compatible:
SQL to add column with default value

See this answer for more details.

edited May 23 '17 at 10:27

Community

1
1

answered Nov 24 '15 at 19:25

Andre

26,751
7
36
80

SQL numeric Error

2 Answers2