Syntax Error in field definition

[Guest]

Hi,

I have a sql statement in Access module to create a table :
'*******************
DoCmd.RunSQL "CREATE TABLE tblInvoiced (IID CHAR(1), INVOICE DECIMAL(6),LINE
DECIMAL(4,0),ACCOUNT DECIMAL (5,0),DATE1 DECIMAL (7,0),WAREHOUSE CHAR(3),MFGR
CHAR(3),COLOR CHAR(3),PATTERN CHAR(9),PRICE1 DECIMAL (9,2),COST1 DECIMAL
(9,2),SLMN (CHAR(3))"
'********************
I checked the references and everything seems to be correct, am I missing a
part of my brain? this gives me an error "Syntax error in field defenition" I
tested it and it doesn't like DECIMAL definition. Can someone help me and let
me know why would I get that error.

Thank you very much.

 

[Allen Browne]

Microsoft has not bothered to update the DAO library to support the Decimal
type since it was introducted 6 years ago.

As a result, your query won't work in DAO code, nor through the normal
mechanisms (such as RunSQL), nor through the query interface (since Access
natively uses DAO.) It should work if you execute the query under ADO, i.e.:
CurrentProject.Connection.Execute "CREATE ...

However, you should also understand that the Decimal type is very poorly
implemented, and Access is not even able to sort the field properly.
Details:
http://allenbrowne.com/bug-08.html

 

[Guest]

Thank you, that worked. I was just reading the difference between ADO and
DAO. I know decimal is not really used anymore, however the data I am pulling
is from AS400(IBM) so I have a lot of those fields. I normally convert it to
double, but sometimes that doesn work very well.

What would be the better data type to convert the decimal to? Especially the
date field: it is decimal (7) and formatted CYYMMDD.

Thanks again.

 

[Guest]

It ran the first time, but if I delete the table and try running it again I
get an error message again, are the limitations?
CurrentProject.Connection.Execute "CREATE TABLE tblInvoiced (IID
CHAR(1),INVOICE DECIMAL(6),LINE DECIMAL(4),ACCOUNT DECIMAL (5),DATE1
DECIMAL(7),WAREHOUSE CHAR(3),MFGR CHAR(3),COLOR CHAR(3),PATTERN
CHAR(9),PRICE1 DECIMAL (9,2),COST1 DECIMAL(9,2),SLMN (CHAR(3))"

 

[Brendan Reynolds]

You appear to have one too many left parenthesis characters in there. I
believe it's the one between 'SLMN' and 'CHAR' in 'SLMN (CHAR(3))'.

--
Brendan Reynolds
Access MVP

It ran the first time, but if I delete the table and try running it again
I
get an error message again, are the limitations?
CurrentProject.Connection.Execute "CREATE TABLE tblInvoiced (IID
CHAR(1),INVOICE DECIMAL(6),LINE DECIMAL(4),ACCOUNT DECIMAL (5),DATE1
DECIMAL(7),WAREHOUSE CHAR(3),MFGR CHAR(3),COLOR CHAR(3),PATTERN
CHAR(9),PRICE1 DECIMAL (9,2),COST1 DECIMAL(9,2),SLMN (CHAR(3))"

 

[Guest]

Duh, Thanks, I am missing my brains today.

 

[Allen Browne]

Alternatives for Decimal:

- Up to 4 decimal places? Use Currency. (It's a fixed point type.)

- Not worried about floating point errors, and no more than 15 places of
precision? Use Double.

- Dates? use Date/Time (or Integer if you want to retain the CYYMMDD.)

- Must use a few Decimal fields? Index them. (Avoids the sorting bug.)