Decimal Data type in Jet DDL SQL?

[Kevin Chambers]

Hi there--

I'm having a heck of a time trying to create a field of data type decimal.
It seems like, according to the docs, the following two statements should
work just fine:

ALTER TABLE Table1 ADD COLUMN Field1 DECIMAL(10,2);
ALTER TABLE Table1 ADD COLUMN Field1 DECIMAL;

Yet both run-time error 3932 "Syntax error in field definition". Using
other data types (bit, money, double, text, etc) there's no problem.

This is Access 2000, using plain old DAO.

Thanks in advance for any insight!

Kevin

[Allen Browne]

Kevin Microsoft did not update DAO to support the creation of a DECIMAL
field type. You need to execute your DDL query under ADO to create the
table, e.g.:

strSql = "ALTER TABLE Table1 ADD COLUMN Field1 DECIMAL(10,2);"
CurrentProject. Connection.Exec ute strSql

Personally, I consider it an advantage of DAO that it does not support this
field type. JET has no idea how to handle it. Would you be happy if it could
even handle as basic a query as this:

SELECT Field1 FROM Table1 ORDER BY Field1;

Details in:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kevin Chambers" <My********@MyN ame.DontSpamMe. UncleSam> wrote in message
news:dq******** *@enews4.newsgu y.com...

Hi there--

I'm having a heck of a time trying to create a field of data type decimal.
It seems like, according to the docs, the following two statements should
work just fine:

ALTER TABLE Table1 ADD COLUMN Field1 DECIMAL(10,2);
ALTER TABLE Table1 ADD COLUMN Field1 DECIMAL;

Yet both run-time error 3932 "Syntax error in field definition". Using
other data types (bit, money, double, text, etc) there's no problem.

This is Access 2000, using plain old DAO.

Thanks in advance for any insight!

Kevin

[Lyle Fairfield]

This is quite a serious drawback to JET decimal fields, Allen. Thank
you for bringing it to our attention.
If anyone is saddled with a Decimal Field, or must use one (I can't
think of any reason why this might be so) I believe one can achieve a
correct descending sort by using an ADODB Recordset as follows:

Sub temp()
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.Open "SELECT * FROM Table3", CurrentProject. AccessConnectio n,
adOpenStatic, adLockReadOnly
.Sort = "a_decimal_fiel d desc"
While Not .EOF
Debug.Print .Collect(0)
.MoveNext
Wend
End With
End Sub

I am not, of course, suggesting that is ideal or convenient.

I disagree with your (web-site) comments about VBA Decimals. They are a
bit clumsy, but they are a simple way to to effect accurate large whole
number arithmetic such as 792281625142643 37593543950335 / 5. If there
is a better way I would be glad to know it.

[Allen Browne]

Thanks for your comments Lyle.

Regarding VBA, sometimes you must use the type (particularly if the value
must be more than 4 decimal places and too large for Currency), but it is
less efficient to use a Variant, requires more scrupulous error-checking,
and you still can't declare a constant of type Decimal AFAIK.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

This is quite a serious drawback to JET decimal fields, Allen. Thank
you for bringing it to our attention.
If anyone is saddled with a Decimal Field, or must use one (I can't
think of any reason why this might be so) I believe one can achieve a
correct descending sort by using an ADODB Recordset as follows:

Sub temp()
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.Open "SELECT * FROM Table3", CurrentProject. AccessConnectio n,
adOpenStatic, adLockReadOnly
.Sort = "a_decimal_fiel d desc"
While Not .EOF
Debug.Print .Collect(0)
.MoveNext
Wend
End With
End Sub

I am not, of course, suggesting that is ideal or convenient.

I disagree with your (web-site) comments about VBA Decimals. They are a
bit clumsy, but they are a simple way to to effect accurate large whole
number arithmetic such as 792281625142643 37593543950335 / 5. If there
is a better way I would be glad to know it.

[Lyle Fairfield]

Allen Browne wrote:

...
and you still can't declare a constant of type Decimal AFAIK.

This is True but quite good results may be achieved with a constant
Variant of SubType string. eg.

Const d As Variant = "79228162514264 337593543950335 "

Sub DecimalAritmeti c()
Debug.Print d / CDec(5)
'15845632502852 867518708790067
End Sub

I have not run any kind of exhaustive testing of this at all.

[Kevin Chambers]

Thanks Allen, et al, for the quick response and useful discussion. I
wasn't aware of (and the help files don't seem to mention) the liabilities
of the DECIMAL type. FLOAT should work fine for my needs. It would be
nice to be able to finely control the precision, but that can be handled
easily enough at the front end.

KC

Allen Browne wrote:

Kevin Microsoft did not update DAO to support the creation of a DECIMAL
field type. You need to execute your DDL query under ADO to create the
table, e.g.:

strSql = "ALTER TABLE Table1 ADD COLUMN Field1 DECIMAL(10,2);"
CurrentProject. Connection.Exec ute strSql

Personally, I consider it an advantage of DAO that it does not support this
field type. JET has no idea how to handle it. Would you be happy if it could
even handle as basic a query as this:

SELECT Field1 FROM Table1 ORDER BY Field1;

Details in:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html

[Allen Browne]

No worries, Kevin.

BTW, Currency is a fixed point type: 4 places, but you can store the
fractions exactly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kevin Chambers" <My********@MyN ame.DontSpamMe. UncleSam> wrote in message
news:dq******** *@enews4.newsgu y.com...

Thanks Allen, et al, for the quick response and useful discussion. I
wasn't aware of (and the help files don't seem to mention) the liabilities
of the DECIMAL type. FLOAT should work fine for my needs. It would be
nice to be able to finely control the precision, but that can be handled
easily enough at the front end.

KC

Allen Browne wrote:

Kevin Microsoft did not update DAO to support the creation of a DECIMAL
field type. You need to execute your DDL query under ADO to create the
table, e.g.:

strSql = "ALTER TABLE Table1 ADD COLUMN Field1 DECIMAL(10,2);"
CurrentProject. Connection.Exec ute strSql

Personally, I consider it an advantage of DAO that it does not support
this field type. JET has no idea how to handle it. Would you be happy if
it could even handle as basic a query as this:

SELECT Field1 FROM Table1 ORDER BY Field1;

Details in:
Incorrect Sorting (Decimal fields)
at:
http://allenbrowne.com/bug-08.html