General question - when exporting the results of an Access query to Excel a part number in Access as 13-0011 turns up in Excel as 40495.
Is there a reasonable explanation for why this happens?
General question - when exporting the results of an Access query to Excel a part number in Access as 13-0011 turns up in Excel as 40495.
Is there a reasonable explanation for why this happens?
sanity check your field type in Access (probably is Text) vs the cell type in Excel....
do a test and force that excel cell type to text; one way is to put some dummy records in the top of your Access table that are clearly text i.e lkjlkjlkjlkjlkjlkj
I've tried this in Access 07 and Excel 07. Attempted using different ways to troubleshoot and continue to get the CORRECT (i.e. 13-001) result in Excel every time. Not sure why Excel is converting your cells to a number.
What version of Excel are you using?
Right click the number cell in Excel and check the Format type. Does it show General type?
When using Access to Output the table or query to Excel, what is the Output Format you are using? i.e. xls, xlsx
-RC
I am using both Access and Excel '03, maybe in '07 this was fixed?
I know it's frustrating when I query the data but the user's want to see the results in Excel. I have warned them to proof the data because I know Excel likes to drop leading zeros but this is the first time I've ever seen it change a number too.
If I copy/paste from Access to Excel - it works like it's supposed too, no pn changes, but when I export from the Access query to Excel - bad news.
Thanks for the ides guys. Keep up the good work. We in user-ville appreciate it.
Sorry Rick,
The reason I was troubleshooting in Access 07 and Excel 07 was because your screen name says you are using Access 2007 version 12.0.
I know you previously marked this thread as solved, but consider NTC's previous recommendation by adding a dummy record into your table. Only thing with going this route though, is that you will need to remove the dummy record every time from your exported Excel doc.
Or you can test on a dummy table, and add an alpha character to all your part numbers. May not be feasible depending on the development stage of your database though.
Regards,
-RC