Parameter Query

[Lee]

I seem to be hitting a limit in the number of parameters Excel takes in
getting data. The query in MS Query does return records alright with four
parameters, but it will not return the data to the worksheet. It does fine
with only three parameters. The parameters are set to read specific cells in
the workbook.

I have modified the query by alternately hardcoding the parameters to see if
any one of them was the problem and the query returns data just fine....so
long as there are only three parameters. Any ideas?

 

[Joel]

You query may not be returning any data becasue there is no data that matches
all 4 filters.

I usually debug my queries using the query editor. The editor will show you
the data that matches the filters in real time and give you errors if your
sql has errors.

From excel 2003
Select a cell wherre data is returned using 3 filters
then from worksheet menu
Data - Import External Data - Edit query

Look for the SQL box and select. Modify the SQL as required and you will
see the items returned from the query. Change the SQL until you understand
the problem.

 

[Joel]

You may need to assign an alias to the column name like below for it to work

SELECT Orders.OrderID AS Freight, Orders.Freight, [Freight]*1.1 AS [New
Freight] FROM Orders;

 

[Lee]

Joel, I had a select DISTINCT in my sql. It looks like that was the problem
although I don't understand why. I have removed the "distinct" command and
am now returning data into the worksheet. I appreciate your time on this.

You may need to assign an alias to the column name like below for it to work

SELECT Orders.OrderID AS Freight, Orders.Freight, [Freight]*1.1 AS [New
Freight] FROM Orders;

I get records in Query Editor. However, I have determined that there is not
a limit on the number of parameters. Instead, it appears that excel does not
like the use of parameters on two specific fields. I can use one of the
fields in conjunction with the other so long as one of them is hardcoded and
one is parameter.

Basically, the question now is why would I get records returned in Query
Editor but not returned in the excel worksheet...still working on the
problem. thanks.

 

[Lee]

I get records in Query Editor. However, I have determined that there is not
a limit on the number of parameters. Instead, it appears that excel does not
like the use of parameters on two specific fields. I can use one of the
fields in conjunction with the other so long as one of them is hardcoded and
one is parameter.

Basically, the question now is why would I get records returned in Query
Editor but not returned in the excel worksheet...still working on the
problem. thanks.