Access/Excel bug? (XP SR1)

Topic

New Reply

I don’t know if this is an Access XP problem or an Excel XP problem, and I’ve never discovered an actual, meaningful bug before, but I’m pretty sure I’ve found one now. I’m going to search for the method to report it to Microsoft but wanted to run it by you to see if anyone has encountered it.

If you’re working in Access with a simple select query containing numbers (mine are Long Int.), and you do a copy and paste of all the data in the query result to a blank Excel worksheet, Excel does something weird with the numbers. Formulas and functions don’t work on them, even an AutoSum at the bottom of the column doesn’t work, but if you add two cells together in a simple formula you’ll get the correct result. I’ve attached a spreadsheet with my query results to illustrate. If you use Access’ “Analyze it with Microsoft Excel” button it works just fine, but the copy and paste method is what my folks are used to and we just moved them from 97 to XP and they’re really sorry to see their preferred method no longer working.

If you’ve got any helpful advice about how to report a bug, or if this is old news already, please let me know.

Reply | Quote

Replies

This is a delimiter issue. Apparently, when the information is being imported into excel, the fields are delimited with specific characters – numbers are preceeded with a ‘. I recommend selecting the problem area(s), and clicking Data > Text to Columns… Click Delimited, click Next, and change the Text Qualifier to {none}. Then Finish up. I think it’ll solve your problems.

I don’t know of a way to get around this at the initial import, but I’ll take a look into it.

–What a frustrating situation. I was lucky to stumble on your solution (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q127132). It, however, is one of those ‘How can I fix it if I don’t know what’s wrong?’ scenarios.

Good luck!

Reply | Quote

Thank you. That does work but it will probably be easier for them to learn to use the ‘Analyze it …’ feature than to go through that each time. I couldn’t see the ‘ and the cells were right-aligned so it sure didn’t look like a text field. I don’t know why this would have changed from 97 to XP but I’ve learned not to ask those types of questions of Microsoft. That you found a solution in the knowledge base tells me Microsoft is already aware of the problem so I won’t go through the process of reporting it.

Thanks again,
Nanette

Reply | Quote