Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Query (Access 2002 SP-2 / Excel 2002 SP2)

    Is it possible to have an Access query that runs fine in Access, but one of the criteria is too complicated for MS Query? (and the criterion isn't all that complex!)

    I am trying to pull some data into an Excel spreadsheet using MSQuery in order to produce a chart. I keep getting an ODBC-Call Failed error. I can add the Access query, but when I try to add any of the fields to the query grid, I get the error. None of my other 3 queries or tables I'm using with MSQuery is causing errors. When I remove the criterion, I can import data from the Access query, but of course it is not the data I want. The query criterion that seems to be having the problems is this:

    <font color=blue>Between ((Year(Now())-2000)*12)+Month(Now())-12 And ((Year(Now())-2000)*12)+Month(Now())-1</font color=blue>

    Allow me to intrepret. The data I'm reporting from is already aggregated by month, with each month designated by a month number (1 = January, 2000). The logic above returns a rolling list of the previous 12 months using this month number. It works in Access, but when I pull said Access query into MSQuery, it fails. If I remove the above criterion from the Access query and run it from MSQuery, it runs, but the data is wrong. What's up with that?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS Query (Access 2002 SP-2 / Excel 2002 SP2)

    What is the length of the SQL string, including and excluding the criteria?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (Access 2002 SP-2 / Excel 2002 SP2)

    390 characters including the criteria string. Criteria string is 84 characters long. I realize that there is a limit to how long the SQL string can be, but surely I'm not close. I know (or at least I think I know) that I've had other queries with longer SQL strings.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: MS Query (Access 2002 SP-2 / Excel 2002 SP2)

    Have you considered the possibility of pushing the data out from Access by using TransferSpreadsheet or by using Automation? I tend to avoid using MSQuery because I occasionally get strange things that don't seem to work, while the Access approach always seems to.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (Access 2002 SP-2 / Excel 2002 SP2)

    Thanks for the suggestion, Wendell. However, TransferSpreadsheet won't work for me since the data is starting in Access. It is pulled into Excel (or sent there) supplying data for an already formated chart, then pulled back into Access. If I Export it, a new spreadsheet (or even file) will be created and the chart won't exist. I don't see a way to tell Access to overwrite an existing range. Is there a way to do this?

    I have no experience creating an Excel chart from automation. If there is no easier way, I can look into this.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: MS Query (Access 2002 SP-2 / Excel 2002 SP2)

    You are correct - TransferSpreadsheet only creates a new workbook, and you can't specify a range. I presume you found the Access charting tools inadequate for your needs, so if you can't get MSQuery to behave, Automation may be your only recourse. Take a look at <!mskb=202169>Microsoft Knowledge Base Article 202169<!/mskb> which tells you how to create an Excel Chart using Automation. If you want more info on Automation, you might find our website tutorial of value.
    Wendell

Posting Permissions

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