• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

Connect to a Crosstab query in Access from Excel

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
0
chrisezard
Asked:
chrisezard
  • 2
  • 2
1 Solution
 
SimonCommented:
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.
0
 
SimonCommented:
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:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22156576.html
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/98f22137-caac-42e4-a229-c9629c957262/
http://www.eggheadcafe.com/software/aspnet/31011992/invalid-character-error-w.aspx
0
 
chrisezardAuthor Commented:
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.
0
 
chrisezardAuthor Commented:
Led me to the answer. Many thanks.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now