SQL Specific Window for numeric data types?

D

debkay_rich

Using SQL Specific Window in Access 03 to practice writing sql. I get a
syntax error in my Create Table whenever I define a numeric data type, such
as Number, Decimal, Integer, Double or Long. The error occurs when I try to
run the query. It always stops at the first parenthesis and issues a Syntax
error.

Example:

PAY_RATE NUMBER(4,2),

All other data types work. The query will run and create the table.
 
A

Allen Browne

You are talking about a DDL query that creates a field?
If so, "NUMBER" is too generic.
Use INTEGER, LONG, DOUBLE, etc., without the size in brackets.

If you are trying to create a DECIMAL size field, the DDL query probably
won't work in the query interface, since DAO has not been updated to support
this. By executing your DDL query under ADO, you should be able to use:
DECIMAL(4,2)

However, you should be aware that even if you do this, the Decimal field
type is not properly implemented, and Access can't even sort it properly.
Details
Incorrect Sorting (Decimal fields)
at:
http://members.iinet.net.au/~allenbrowne/bug-08.html

For a list comparing the field types names and contstants to use for the
various libraries, see:
Field type names (JET, DDL, DAO and ADOX)
at:
http://members.iinet.net.au/~allenbrowne/ser-49.html
 
A

Allen Browne

Hi Jamie

Just had a quick look, and the answer appears to be that an unsized TEXT
field is created inconsistently.

If you execute the DDL query under ADOX, you do get a Memo field
(adLongVarWChar).

But if you execute it under DAO, it looks like you get a standard Text field
(adVarWChar).

Does that match what you see?
 
J

Jamie Collins

Allen Browne said:
Just had a quick look, and the answer appears to be that an unsized TEXT
field is created inconsistently.

If you execute the DDL query under ADOX, you do get a Memo field
(adLongVarWChar).

But if you execute it under DAO, it looks like you get a standard Text field
(adVarWChar).

Does that match what you see?
Click to expand...

Yes, it does. Thanks Allen.

Jamie.

--
 
Top