Export to Excel - Not keeping 0s on Text fields

[manrique83]

I am exporting a report to Excel and everything looks fine, except that I have a field, Social Security Numbers, where it doesn't keep the format once it has been exported to Excel.

For example,
SSN 0012345678 in Access
will export to
1234567
deleting all the leading 0s

I checked the data type on the table and it is a Text Field, but Excel reads it as General type.

Can someone help?

 

[BSman]

Excel automatically treats anything that looks like a number as a number. You might try exporting the SSN with an added leading character (such as an underscore) or as formatted (nnn-nn-nnnn), since with the leading character or the two dashes it probably won't be treated as a number (unless Excel decides that it's an equation).

The other alternative is to put double quotes around the SSN when you export it. I believe that may also force Excel to recognize it as text.

Bob S.

 

[manrique83]

Thanks Bob, but where and how would I code that?

Thanks,
Rod

 

[BSman]

Create a query on the table where you include the following as one of the columns (assuming the column in your table is called SSN):

SSNtext: "'" & [SSN] & "'"


This will put single quotes around the SSN.

Bob S.

 

[manrique83]

Thanks Bob! Simple solution!