Microsoft Query-Chg parameters using VBA

**yzer19** · 11-16-2007, 10:32 AM

I currently pull data out of our dealer management system thru Microsoft Query and then into Excel. Is there any way to build a spreadsheet so that the user can change the criteria of the query without having to go into the query builder. The change that would be needed would be something like a date range.
Something like this.
-The user enters a date in cell A2 of the spreadsheet and hits enter.
-The VBA code cuts and pastes the date into the criteria field of the query under the date field.

I think this is possible as Ive seen it done but I'm not sure how.

Thanks

**Ron Coderre** · 11-16-2007, 10:53 AM

Here you go....

In MS Query, build the query per normal.
View the Criteria window
Select the table field(s) that you want to supply cell parameter values for

For the criteria:
Enclose the parameter name in square brackets
Example: [prmCustNum]

When you run the query, you will be prompted to supply each parameter.

After the query runs properly, click the Return Data to Excel button.
On the popup window where you select the destination for the query data,
there is a [Parameters] button at the bottom.

Click the [Parameters] button
All parameters will be displayed
Select a parameter
Select "Get the value from the following cell"
Select the cell containing the parameter value
Repeat for each parameter

After completing the process, whenever you refresh the query, the values
from the parameter cells will be supplied to the SQL.

Thereafter, if you need to change the location of the parameter cells:
Right-click on the data table
Select: Parameters
That will open the Parameters window, where you will effect the changes.

Does that help?

**yzer19** · 11-16-2007, 12:13 PM

Ron,
Yes that gets me going in the right direction for sure. A couple of things happened.
-When I try using a field labelled "Recd Date" it works fine if I enter the date each time the report refreshes. If I try to have the date entered in a cell and automatically update I get this error "[Sybase][ODBC Driver][Adaptive Server Anywhere]Restricted data type attribute violation: Cannot convert 05/01/2007 to a timestamp". I have tried to change the format of the cell but it doesn't seem to make a difference.
-Some of my reports I bring the data into a pivot table. When I try to use the [xx] method it tells me that "Parameters are not allowed for this query" Will this method only work if the data is brought back into excel in a list form?

Thanks for your help.
Rob

**Ron Coderre** · 11-16-2007, 12:37 PM

1) Not sure what Sybase SQL is looking for. You may need to wrap the parameter in a date conversion function (in Oracle, it would be TO_DATE(param_value)).

2) It looks like basing a Pivot Table directly on a parameter query will be a problem. If you can't use the PAGE option for the Pivot Table, we may need to explore VBA options.

**yzer19** · 03-28-2008, 09:51 PM

I guess I will need some VBA to pull this off, does anybody have any ideas. The parameter values that Ron mentions works perfect but my problem is the data is built into a pivot table. The reason for this is the number of lines in a spreadsheet limits the data pull and the pivot table does a nice job of summarizing a large amount of lines.
-I need to find a way to get the parameter values to work with a pivot table. I want the user to be able to change the criteria in MS query without having to access the query builder.
Any ideas would really help.
Tks

**Ron Coderre** · 03-29-2008, 10:38 AM

This might be a good application for the free
Pivot Play PLUS add-in, located at Debra Dalgleish's website:
http://www.contextures.com/xlPivotPlayPLUS01.html

It allows you to view/edit the Data Source and SQL code
for Pivot Tables and Query Tables that use external data sources.

One of its features may be helpful in your situation. Pivot Play PLUS
can import the SQL code from a cell. Once you had the base query
created in MS Query, you could copy the SQL and build a formula
based on it that incorporates the parameter values. If fact, you
could build several different variations of the SQL code in several cells
and use the add-in to select which cell to use.

Is that something you can work with?

**yzer19** · 03-29-2008, 11:08 PM

Thanks Ron that is a very neat program you have developed. That will help me in some other areas but it doesn't solve my problem of making the spreadsheet very simple to use for the basic user I'm dealing with.
I think I have a solution to my problem but I'm stuck on the last step.
-I have created a database query that pulls my data from our inhouse software package (I think its an ODBC database). I figured out how to sum a field in MS Query which drastically decreases the amount of lines on my spreadsheet. My final report is a financial report that is summarized monthly.
-Because this is a database query and not being pulled directly into a pivot table I can use the parameter values
-On another sheet I can base my pivot table off the database query.
My problem is when trying to use parameter values and a sum field I get the following error "Invalid column name 'Acc' Statement(s) could not be prepared. The error confuses me as I haven't renamed any columns. If I delete the sum column the parameter value works fine.

Here is the SQL code from MS Query if this helps

Please Login or Register  to view this content.

My end goal is to have a simple way for the user to change the query parameters without actually having to go into MS Query. The end report must be a pivot table. Hope this makes some sense.
Thanks