Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MSQuery issue.

Posted on 2010-09-01
4
411 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

828 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