Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Location
    Nashville, Tennessee, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access/Excel bug? (XP SR1)

    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.
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access/Excel bug? (XP SR1)

    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...;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!

  3. #3
    New Lounger
    Join Date
    Dec 2001
    Location
    Nashville, Tennessee, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access/Excel bug? (XP SR1)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •