Solved

MSQuery issue.

Posted on 2010-09-01
4
403 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
ID: 33583905
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
ID: 33585928
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
ID: 33587414
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
ID: 33587989
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delete all words in cell more than 5 words 7 47
ms office troubleshooting for users 8 36
TSQL previous 5 25
Vlookup formula error 15 12
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Outlook Free & Paid Tools
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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.

867 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

18 Experts available now in Live!

Get 1:1 Help Now