export to excel converts text to scientific notation

[Guest]

When I use the transfer spreadsheet action to export data from access to
excel, the excel converts the Access's text format value '939336E90' to
scientific notation value '9.39E+95' yet when you check the cell format in
excel it shows it as text. This only occurs on some computers in the company
and not on others. In addition, on the computers where it doesn't change it
to scientific notation it doesn't keep the format of the cell consistent from
computer to computer. On some computers it changes the format of the cell in
excel to general format from text format, while it doesn't on some computers.
All of the excel settings for the various computers seem to be the same.
This problem only occurs in the records where there is one letter character
in the string value and it is E. Does anyone know of this issue and have a
solution? Thanks in advance.

 

[John Nurick]

I can't reproduce this here (Access 2003 SP1, WinXP SP2): when I export
a table or a simple query to Excel the system prefixes each value in a
text column with a apostrophe, which forces Excel to treat it as text
regardless of value.

Are these values coming direct from a table, or are they calculated
fields in a query you're exporting? If the latter, try including an
apostrophe in the expression, so you're exporting
'939336E90
rather than
939336E90

Another thing I'd do is to compare the settings in the registry key
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/
, looking for differences between the various machines. See

http://www.dicks-blog.com/excel/2004/06/external_data_m.html
and http://support.microsoft.com/?id=257819 for information on what
these settings do (although it's not clear - at least to me - how they
affect data types on export).

 

[Guest]

John,

Thanks for the response -
The data resides in a local table in the access database as a datatype text
and is made available to the transfer spreadsheet action through a query that
is inheriting the text datatype (field in the query is not specified to a
datatype within the query).

One additional piece of info that might be useful is that the field in the
query is an expression that evaluates a condition through an if statement and
selects value from 1 of 2 fields that are residing in the local table in
access db (see above) as a datatype text. Is the text datatype lost through
the expression?

 

[Ronald Roberts]

John,

Thanks for the response -
The data resides in a local table in the access database as a datatype text
and is made available to the transfer spreadsheet action through a query that
is inheriting the text datatype (field in the query is not specified to a
datatype within the query).

One additional piece of info that might be useful is that the field in the
query is an expression that evaluates a condition through an if statement and
selects value from 1 of 2 fields that are residing in the local table in
access db (see above) as a datatype text. Is the text datatype lost through
the expression?


:

Try what John said in his reply.
Add an apostrophe to your expression.

Result: "'" & Your_expression_or_calculation

This should keep it as a text field in Excel.

Ron