Importing Numbers in Scientific Notation

[PJFry]

I am automatically importing excel 2007 files that have 15 digit numbers that
come across formatted in SN. For example 100000006290717 is formatted as
1.00E+14. These files are appended to a table where that field is Double.
The problem I have is that the value that ends up in the table is
100000006290000 instead of 100000006290717.

The import section of the code I am using to import these files is

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tShipping", sPath & sFile, False

Any thoughts on how to correct this?

Thanks!
PJ

 

[John W. Vinson]

I am automatically importing excel 2007 files that have 15 digit numbers that
come across formatted in SN. For example 100000006290717 is formatted as
1.00E+14. These files are appended to a table where that field is Double.
The problem I have is that the value that ends up in the table is
100000006290000 instead of 100000006290717.

The import section of the code I am using to import these files is

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12,
"tShipping", sPath & sFile, False

Any thoughts on how to correct this?

Thanks!
PJ

Are these actually *number values* that you will be using for calculation? Or
are they some sort of identifier? If the latter, import them as Text. You may
need to use Excel to change 100000006290717 to '100000006290717 to
specifically define it as a text value, or put a text value in the first row
of the spreadsheet to force Access to interpret the values as text.

Note that the format is irrelevant, and that Double fields are floating point
binary *approximations* with about 14 decimal places precision. You won't be
able to reliably get these values into Doubles.

 

[Chegu Tom]

If you are using this number for some kind of calculation,,,

Do you really have 15 significant digits accuracy in your measurement of
this item?

 

[John W. Vinson]

If you are using this number for some kind of calculation,,,

Do you really have 15 significant digits accuracy in your measurement of
this item?

If so you should use a Decimal number type rather than Double. A Double will
give you 48 bits precision, about 14 decimals - it won't reliably give you 15.

(It'll give you 15 just often enough to fool you into trusting it, in other
words).