Guidelines for creating numeric fields with CreateField

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

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?
 
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
 
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.
 
Back
Top