Connect to a Crosstab query in Access from Excel

Posted on 2010-01-08
Last Modified: 2012-08-13
I am connecting to Accesss 2003 from Excel 2003 using an ODBC connection. I want the query results to load directly into Excel. This works with Select queries not with the Crosstab query I want to see. Is this simply not possible and, if so, why? Otherwise could you help, please? I am getting a message box with this message...
[Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast specification on column number 28 (9)

Many thanks
Question by:chrisezard
    LVL 18

    Accepted Solution

    It should work. I've just tested with Access and Excel from OfficeXP.

    The error message you're getting suggests it is perhaps just one column that is causing you the problem. Try making a copy of your crosstab query and simplify it for testing. Try omitting whatever appears in column 28 and see if it works then. Sounds like Excel is trying to convert data and failing.
    LVL 18

    Expert Comment

    Also note that you should be able to do either of the following:

    1. Choose export from the file menu (with the crosstab query selected) to export the query results to Excel
    2. Choose Tools/Analyze it with Excel to export the data to Excel.

    You _may_ get a more helpful error message via one of those methods. I only did a simple crosstab on sales data (period/item/quantity/total value) but it worked without issues.

    You might also benefit from reading these links:
    LVL 1

    Author Comment

    Thank you very much for your comments. I ran a simple crosstab query and proved that Excel could read it. Then after some experimentation, as you suggested, I found the query worked when certain items were omitted. The query had several joins so the solution was to build the Crosstab from the original Select query, making the Crosstab SQL much simpler. Then it ran fine.
    LVL 1

    Author Closing Comment

    Led me to the answer. Many thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    VBA Excel Gantt 1 24
    Min Month2 5 16
    Access left join query 5 7
    Access Query concatenation 12 15
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now