Guidelines for creating numeric fields with CreateField

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 23 Jan 2006 22:35 GMT

I have a data dictionary file with field types and lengths which is meant to be
used for programmatically creating a table. In this file the definitions for
numeric fields specify the total number of digits and also the number of digits
to the right of the decimal separator. I intend to use dbInteger for cases
when the number of digits to the right of the decimal separator is zero, and
dbCurrency when it's not. My concern arises from the fact that dbCurrency
fields are guaranteed accurate up to four digits of the decimal sign and in
some instances the dictionary shows fields with five digits to the right of the
decimal sign. After I create the table I will be using it for importing data
from a text file. Is there a better approach for handling of numeric fields
than what I'm thinking of doing?

Reply to this Message

Allen Browne - 24 Jan 2006 03:26 GMT

If you need more than 4 decimal places, the Currency field is not suitable.

Any chance of getting away with a Double? Although it is actually a
precision level rather than a scaled number, and therefore subject to
rounding issues, it is probably your best shot unless the numbers consist of
too many significant digits.

If that is not possible, JET does have a field type called DECIMAL, which is
a scaled number of up to 28 places. You will need to create the field with
ADO, e.g.:
CurrentProject.Connection.Execute _
"ALTER TABLE Table1 ADD COLUMN MyDecimalField DECIMAL (28,5);"

You should also be aware that JET is unreliable even with the most basic
operations for this data type, e.g.:
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.

>I have a data dictionary file with field types and lengths which is meant
>to be
[quoted text clipped - 16 lines]
> fields
> than what I'm thinking of doing?

Reply to this Message

Alex - 24 Jan 2006 14:28 GMT

Thanks Allen. I think I will go with Double for those cases where the
dictionary specifies more than 4 decimal places, and use either Currency or
Integer for the rest.

Reply to this Message