MS Query crash in Excel 2000

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a saved query that was created in MS Query. I use it to store the
results in Excel 2000 (my employer's version, not my choice! v9.0.6926 SP-3).
I could not use the Query Wizard to create it because the SQL was too
complex. At least that's what the app told me. So I just opened a SQL
window, pasted the SQL into it, then executed it and dumped it to Excel.
Works very well except I perceive that there's a bug in Excel that prevents
me from editing the query again. The SQL selects on a data attribute and I
need to execute it every week. All that needs to change is the date value.
I open the Excel file, save it as a new worksheet, then open the saved query.
When I change the date value in the SQL window, Query crashes (I can include
the dump if that helps).

Since all I need to do is change the date value every time I run the query,
I thought about making that attribute a prompt. However, I'm unable to
toggle the Show/Hide Criteria button to show the criteria and create the
prompt. I assume that's because the query can't be represented graphically
(going back to the complex issue).

Can anyone provide assistance with either the Query crash or how to
establish a prompt using SQL alone? Thanks!
 
Badun

I can't give you a reason for the crash, but this page will show you how to
query any external data (SQL is fine) with a parameter in Excel, so you
could simply change the date in the worksheet to pull through the correct
data

If this doesn't work because the SQL is too complex for MSQuery to parse,
then set up the query in SQL and use that in MSQuery. (Bear in mind you
cannot have a variable parameter in the query in SQL, you will need to just
join the tables and exclude any data with 'fixed' parameters, then set up
the variable parameter(s) in MSQuery)

Link

http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk
 
It was something weird with the proxy server at work. Works fine now and
thanks for the tips!
 
Back
Top