By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,795 Members | 1,761 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,795 IT Pros & Developers. It's quick & easy.

Decimal Data type in Jet DDL SQL?

P: n/a
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
Jan 18 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

--
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********@MyName.DontSpamMe.UncleSam> wrote in message
news:dq*********@enews4.newsguy.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

Jan 18 '06 #2

P: n/a
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.

Jan 18 '06 #3

P: n/a
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.googlegr oups.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.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.

Jan 18 '06 #4

P: n/a
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 = "79228162514264337593543950335"

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

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

Jan 18 '06 #5

P: n/a
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.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

Jan 18 '06 #6

P: n/a
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********@MyName.DontSpamMe.UncleSam> wrote in message
news:dq*********@enews4.newsguy.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.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

Jan 18 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.