Parameter Query in excel 97 3

[sbhitchc]

How do I specify a cell as the input for a parameter query?

Thanks,
Scott.

 

[SkipVought]

Hi,
What is a parameter query? Are you doing an MS Query?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sbhitchc]

Yes, I am using MS Query. Instead of requesting input from the user such as [Enter Input Here] as a parameter to the query I would like to use the text in a cell as the parameter.

 

[SkipVought]

OK, so you know how to use MS Query.

Macro record editing your query.

Then look at the resulting code. You will have an SQL parameter or QueryText or something like that. This is where your parameter will have to be incorporated. For instance, If your query is...

sQuery = "SELECT * FROM Customers Where CustNbr='" & [YourCell] & "' "

Substitute sQuery for the actual query text.

There's a little more to it, but that will get you close. You will have to run this on command. You could trigger it from the user entering the parameter using the worksheet change event.

Let me know how you are proceeding.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Actually - you don't need to look at the SQL - in the design view of the query, click on the add criteria button
Then, in the appropriate field, just as you would in an Access query, put
=[enter value]
Save the query when done. When you return the data to excel, you will be propmted for how you want to specify the parameter and a cell on a worksheet is one of the options

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

KEWL!

That's why xlbo is Tipmaster of the Week!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

Soooooooooo damn useful for those of us that suck at SQL

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Wray69]

Hey Geoff,

Congradulations of Tip Master of the week, you deserve it man.

Cheers,

Wray

 

[xlbo]

Thx Wray

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[rnuage]

Thanks for these tips- I've been trying to figure this out for a couple of days. Skip's suggestion worked perfectly!