Field Conversion Occurring when I Send-to-Excel

[Guest]

We have a number of reports in Access 2000. We use the built-in function
Send-to-Excel to enable our users to send the Report to Excel. We are
noticing that some of the field data is different in Excel than it was in the
original Access Report. For example it converts a Part Number like 23-1-0312
to a number -579982. Another example is converting a part number such as
4041-008 to 782199.

I suspect this is Excel automatically converting these; however, I do not
know why and how to block that from happening. Suggestions?

Regards,

Robert

 

[Allen Browne]

The first example seems to be Access converting the string to 23 January in
the year 312 AD. You can verify the result by opening the Immediate Window
(Ctrl+G), and entering:
? CDbl(CDate("23-1-0312"))
The second one is less obvious.

Any chance of exporting a query rather than a report?
You can explicitly force the data types in a query.
And you can set up an Import/Export specification so Acces has information
about how to convert the fields.

 

[Guest]

Hi Allen

Thank you for your helpful response. I suspected this may be what is
happening but could not understand the relationship.

We have over 150 reports so we could change this; however, you can probably
imagine the level of effort that is involved in doing this. I suspect an
easier solution may be to add a character to the Part Number to force it to
convert as text only.

Regards,