Export to Excel changing datatype

  • Thread starter Thread starter notDave
  • Start date Start date
N

notDave

I have a report that I'm exporting to Excel. One
particular field is giving me grief. The field happens to
be a Social Security Number, but the datatype in the table
is Text because some SSN's begin with a zero. (If it's
not a text field Access drops the beginning zero(s).) The
field displays fine on the report, but when I export it to
an Excel file, and then open the Excel file, the field has
reverted to text. I've tried putting format
([MyField],"000000000") in both the query and the Control
field of the report to no avail.

When I export the exact same report to rtf or snapshot
filetypes the data is correct, which indicates the issue
may be on the Excel side but I still don't know what to do.

Any suggestions?

~notDave
 
I'm sorry, I misspoke,

"... the field has reverted to a number in Excel, thus
dropping off the leading zeros."
 
I don't know a straightforward way round this when exporting a report to
Excel.

If you export the underlying query instead the text field should come
through OK.

Alternatively, if you format the SSN so it can't be interpreted as a
number, it should get to Excel as text. Don't SSN's usually have a
hyphen or two in them? If so, how about including them in the format
string? Or if you prefix the SSN with an apostrophe, this forces Excel
to treat it as text. The apostrophe may appear in Excel at first, but
will disappear if you update the cells, e.g. by clicking on them,
hitting F2 and then Enter.

This Excel macro will do the same job for the selected cells:

Dim C As Excel.Range
For Each C in Selection.Cells
C.Formula = C.Formula
Next

I have a report that I'm exporting to Excel. One
particular field is giving me grief. The field happens to
be a Social Security Number, but the datatype in the table
is Text because some SSN's begin with a zero. (If it's
not a text field Access drops the beginning zero(s).) The
field displays fine on the report, but when I export it to
an Excel file, and then open the Excel file, the field has
reverted to text. I've tried putting format
([MyField],"000000000") in both the query and the Control
field of the report to no avail.

When I export the exact same report to rtf or snapshot
filetypes the data is correct, which indicates the issue
may be on the Excel side but I still don't know what to do.

Any suggestions?

~notDave
Click to expand...
 
Back
Top