Solved

MSQuery issue.

Posted on 2010-09-01
4
397 Views
Last Modified: 2012-05-10
I have an excel spreadsheet with data that was inserted into a table from an ODBC source. Everything works fine until I use a criteria to filter the data. I am able to return the results the first time with the criteria filter, however when I then attempt to change criteria value or field I have issues.
I right click on the table in excel and select table, then edit query, and I am given a message from Microsoft Query stating "This query cannot be edited by the Query Wizard". If I press OK the Microsoft Query screen opens without the tables section on the top of the view, and all of the options to add a table or criteria from the drop down menus are greyed ou, and the toolbar buttons are unresponsivet. I am unable to edit the query.

Does anyone know of a solution to this issue? At the moment each query has to be recreated each time I need to change the criteria.
0
Comment
Question by:adowding
  • 2
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
J79123 earned 500 total points
Comment Utility
This happens when the query is too complex to use the visual editor. It's not lost, it just seems like it.  The SQL statement still exists. you can view the SQL query that is pulling the data. I believe the option to edit the sql query is under the file or edit menu.
If it is just the criteria that is changing, and it is something you plan on changing frequently,  you can change the sql statement to use parameters (cell values) so that you can change the parameters in excel without opening up msquery and editing the SQL statement each time.
To do this, everywhere in the SQL statement that references a value, say
Select from xxx where yyy=#
change the value of the number to a ? And save it or hit ok.

basically, the ? In a SQL statement will tell excel that those are variables/parameters.

When you do this, the query data will go blank because the sql doesnt know quite what to pull yet, then you have to exit back to excel. Then in the properties of the data link, you will have the option to edit the parameter you set (the ? Value) and assign it to a cell.

It's sort've difficult for me to paint this how-to picture without an example. If you can give an example of the query and you need to be able change or update, I'm sure myself or someone else maybe able to give you more precise information.

But the short answer to your question is that, the data pull you are using is too complex to use just the visual editor (the tables etc.) and you need to edit the SQL statement.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
Comment Utility
J79123 is correct that the query is not lost when the SQL statement is too complex for the user interface to display.  Something like an INNER JOIN will usually cause it.  The SQL button will be functional and from the menus it is under VIEW / SQL... From there you can change the SQL statement but the SQL verbs are limited.

However - if the SQL statement is too complex to display in the User Interface the Parameters option will not function.  I do not believe you will have the option to use the "?" in the SQL statement.

If you are pulling your data from an ODBC source such as SQL Server or Access then it may be easier to create a VIEW on the main source that handles the complex SQL and then call that VIEW from MS-Query.  Then you can use the built in Parameters that J79123  described above.
0
 
LVL 6

Expert Comment

by:J79123
Comment Utility
Actually you do get some sort of message about parameters, But it's misleading, because you can use them.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
Comment Utility
J79123 - You are correct...   I found a way to force the parameters to work with a query that would not display in the user interface.  Thanks!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

763 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

7 Experts available now in Live!

Get 1:1 Help Now