Export "Text" Number to Excel

[AJD10]

Hi,
I have a text field in Access (long file name plus misc letters and numbers). I parse 35 characters from there and need to export my query to Excel. However, when I put it into Excel, it changes from "1233439482344089" to "123E+34". Then if I reformat it, it becomes "1230000000000". So, my original values are lost.

If I copy/paste 1 row from Access to Excel, it's fine. However, I need to select 65K records from Access. So, when I paste my Query output, all the fields get truncated.

I'm not sure if I should fix this somewhere in my Access query? Or, do it in Excel?





Thanks,

 

[Blorf]

I have had this Problem, and my solution is not probably the best, but here it is.

Use a query for export, and have it use your value, preceeded by a text charachter, like "T"

so in your query it says Value: "T" & [Yourfield]

Then, when you open excell, select the row, and do a search and replace for T, and replace it with nothing.

Leaves it as text.

Hope it helps, though it kinda stinks.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[wemeier]

You may want to convert the output field of your query to a string using the CStr function. For example:

SELECT CStr([tablefield]) AS Expr1 FROM table;

I tried this and it exported the field as a text field into Excel, preserving the entire value.

Cruising the Information Superhighway
[sub] (your mileage may vary)[/sub]