A rather odd SQL quirk in Access

[upnxT]

Hello all,

I have a rather random question (or query...).

I am doing this little example to demonstrate some SQL commands via Access. It is a CREATE TABLE. I run it like so:

CREATE TABLE temperature

(

city VARCHAR(30),



state VARCHAR(30),

high INT (3),

low INT(3),

;

-----------------------

The syntax seems to come up fine on sites like SQL fiddle, but Access hangs on the bracket just after INT (if I try to change to DECIMAL it just hangs on that instead).

I'm very confused as these are rather simple commands and I've never had this trouble before.

[CJ_London]

you have a superfluous comma after your last int and a superfluous bracket before city

[upnxT]

I removed both and it just moved the syntax error to the first VARCHAR.

I can understand removing the last comma.

Here is what I have now, same problem:

CREATE TABLE temperature

(

city VARCHAR(30),

state VARCHAR(30),

high INT (3),

low INT(3)

)

;

[pbaldy]

Try

CREATE TABLE temperature

(

city VARCHAR(30),

state VARCHAR(30),

high long,

low long

)

[CJ_London]

What is the error you are actually getting?

not sure if varchar is correct for access (perhaps it is for 2016) - try text. And int may be integer?

See this link for datatypes in Access

http://www.w3schools.com/sql/sql_datatypes.asp

[pbaldy]

varchar will work fine in Access, I tested the SQL I posted.

[orange]

Another example of Create Table with MS Access SQL showing Int

[pbaldy]

Another example of Create Table with MS Access SQL showing Int

I don't see Int.

Int will actually work alone, it's the "(3)" that seems to cause a problem. I assume Access doesn't understand it.

[orange]

ST_ID int PRIMARY KEY,

Good point with the (3) Paul.

[pbaldy]

Old eyes got distracted by Integer and missed Int.

[orange]

Join the club, I was focused on int and overlooked/didn't see the (3).

[upnxT]

Hi all,

sorry to be simplistic, but what would the problem with the 3 be here then?

The error I get is that it hangs on the bracket before 3. I have added a screenshot.

[orange]

You are trying to tell Access how to reserve storage for an Integer (number datatype). You have 2 choices -set by Access --integer or Long ONLY. Unlike Text or char or varchar where you can provide "the amount of storage to be used as default". There may be more details, but that's it in overview.


Microsoft Access Data Types

Data type	Description	Storage
Byte	Allows whole numbers from 0 to 255	1 byte
Integer	Allows whole numbers between -32,768 and 32,767	2 bytes
Long	Allows whole numbers between -2,147,483,648 and 2,147,483,647	4 bytes
Single	Single precision floating-point. Will handle most decimals	4 bytes

[upnxT]

Hmm, it seems that if I simply remove the restriction of the number it works, i.e. remove (3).

Is this normal?

[upnxT]

You are trying to tell Access how to reserve storage for an Integer (number datatype). You have 2 choices -set by Access --integer or Long ONLY. Unlike Text or char or varchar where you can provide "the mount of storage to be used as default". There may be more details, but that's it in overview.

I am getting it now! Just for my own head's visualisation what command should I be using then?

high [then what?]