Re: export to excel converts text to scientific notation

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 spread*** 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?


"John Nurick" wrote:

> 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).
>
> On Mon, 22 Aug 2005 14:11:01 -0700, sale10
> <sale10@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> >When I use the transfer spread*** 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 [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
.