Decimal Data type in Jet DDL SQL?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin Chambers - 18 Jan 2006 08:13 GMT

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

Reply to this Message

Allen Browne - 18 Jan 2006 08:51 GMT

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.Execute 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

Signature

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.

> Hi there--
>
[quoted text clipped - 13 lines]
>
> Kevin

Reply to this Message

Lyle Fairfield - 18 Jan 2006 12:43 GMT

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.AccessConnection,
adOpenStatic, adLockReadOnly
.Sort = "a_decimal_field 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 79228162514264337593543950335 / 5. If there
is a better way I would be glad to know it.

Reply to this Message

Allen Browne - 18 Jan 2006 12:54 GMT

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.

Signature

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.

> This is quite a serious drawback to JET decimal fields, Allen. Thank
> you for bringing it to our attention.
[quoted text clipped - 22 lines]
> number arithmetic such as 79228162514264337593543950335 / 5. If there
> is a better way I would be glad to know it.

Reply to this Message

Lyle Fairfield - 18 Jan 2006 13:36 GMT

> ...
> 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 = "79228162514264337593543950335"

Sub DecimalAritmetic()
Debug.Print d / CDec(5)
'15845632502852867518708790067
End Sub

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

Reply to this Message

Kevin Chambers - 18 Jan 2006 21:36 GMT

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

> 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
[quoted text clipped - 13 lines]
> at:
> http://allenbrowne.com/bug-08.html

Reply to this Message

Allen Browne - 18 Jan 2006 23:43 GMT

No worries, Kevin.

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

Signature

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.

> 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
[quoted text clipped - 21 lines]
>> at:
>> http://allenbrowne.com/bug-08.html

Reply to this Message