Excel SQL query
 

Dear all,

I'm trying to add to my Excel spreadsheet a query that goes to the external
database (also an Excel file) and of course pulls out certain data. The
problem is that the typical query wizzard enables entering as a parameter
only fixed value. My question is if it's possible to add as a criteria
valule reference link to selected cell in the spreadsheet. In other word to
make the query take the parameter form the certain cell in the spreadsheet.
Here is an example of condition:
........WHERE (`Sheet1$`.Year=2005) AND (`Sheet1$`.Month=6).......I'd like
the 'month' parameter to be taken from the specified cell ....how to do it ?
Is it possible in Excel SQL ?
Thanks in advance for your help...
cheers

Excel SQL query
 

gogo wrote:
I'm trying to add to my Excel spreadsheet a query that goes to the
external database (also an Excel file) and of course pulls out
certain data. The problem is that the typical query wizzard enables
entering as a parameter only fixed value. My question is if it's
possible to add as a criteria valule reference link to selected cell
in the spreadsheet. In other word to make the query take the
parameter form the certain cell in the spreadsheet. Here is an
example of condition: .......WHERE (`Sheet1$`.Year=2005) AND
(`Sheet1$`.Month=6).......I'd like the 'month' parameter to be taken
from the specified cell ....how to do it ? Is it possible in Excel
SQL ?

You need to set up the condition as a prompt parameter, then change it to a
range parameter. If you're using MSQuery, you would use brackets

..Month = [Enter Month]

to create a prompt parameter. Then, back in Excel, you click on the
Parameters button of the External Data Toolbar and change the parameter type
to Range.

If you're doing this in SQL or VBA (by building a SQL string), it's the
question mark (?) that holds the place of the parameter

..Month=?

Again, you click the Parameters button to change the type.

See http://www.dicks-clicks.com/excel/Ex...htm#Parameters for more
information and examples.


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

Excel SQL query
 

Dick you are the BEST ! Thanks a lot for your help....you saved me a lot of
work and frustration ;-)


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

Excel SQL query
 

one more request , what needs to be entered to the cell in order to select
all items of the parameter e.g. all months from the range....tried * but
doesn't work

U¿ytkownik "facet" napisa³ w wiadomo¶ci
...
Dick you are the BEST ! Thanks a lot for your help....you saved me a lot
of
work and frustration ;-)


--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

Excel SQL query
 

facet wrote:
one more request , what needs to be entered to the cell in order to
select all items of the parameter e.g. all months from the
range....tried * but doesn't work


As far as I know, you can't. Here's a workaround:

Put two parameters even though you only need one; a greater than or equal to
and a less than or equal to. Like this:

SELECT CUSTOMER.Name, CUSTOMER.City
FROM CUSTOMER
WHERE (CUSTOMER.City=? And CUSTOMER.City<=?)

Now in Excel, point the first parameter to J1 and the second parameter to
K1. You can automatically refresh on J1 changing, but not K1. In K1, put

=IF(LEN(J1)=0,"zzzz",J1)

Where zzzz is the highest possible value for whatever datatype you're using.
In this example, if you put Omaha in J1, then Omaha will appear in K1 and it
will return all customers from that city. If you delete J1, then K1 will be
zzzz and it will show all customers.

A little kludgy, but that's the best I could do. I've crossposted this to
the querydao group in case someone over there knows a better way. I've set
the follow up back to programming, so any responses will show up here.

--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

Excel SQL query
 

Once again thanks a lot for your efford.... this would resolve my issue in
110% ;-)


U¿ytkownik "Dick Kusleika" napisa³ w
wiadomo¶ci ...
facet wrote:
one more request , what needs to be entered to the cell in order to
select all items of the parameter e.g. all months from the
range....tried * but doesn't work


As far as I know, you can't. Here's a workaround:

Put two parameters even though you only need one; a greater than or equal
to
and a less than or equal to. Like this:

SELECT CUSTOMER.Name, CUSTOMER.City
FROM CUSTOMER
WHERE (CUSTOMER.City=? And CUSTOMER.City<=?)

Now in Excel, point the first parameter to J1 and the second parameter to
K1. You can automatically refresh on J1 changing, but not K1. In K1,
put

=IF(LEN(J1)=0,"zzzz",J1)

Where zzzz is the highest possible value for whatever datatype you're
using.
In this example, if you put Omaha in J1, then Omaha will appear in K1 and
it
will return all customers from that city. If you delete J1, then K1 will
be
zzzz and it will show all customers.

A little kludgy, but that's the best I could do. I've crossposted this to
the querydao group in case someone over there knows a better way. I've
set
the follow up back to programming, so any responses will show up here.

--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com