Parameter Query

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Mar 11, 2004

• #2

Go into design view, enter something like the following in the parameter field:

[MyParamter]

This creates an input box in MSQuery. Now deliver your query back to Excel. Right-click in the query, select Parameters, and choose the option to get the parameter from a worksheet cell.

This is from memory, if it's slightly off, I apologize.

 

Upvote 0

goblin

Active Member

Joined

Apr 16, 2003

Messages

469

• Mar 11, 2004

• #3

First, if you get a warning that the query cannot be displayed in Microsoft Query you cannot use parameters. So anything complex is ruled out

Anyway, put question marks inside the query where you want to to place a parameter. When you press OK, Microsoft query will ask you for values for each ? in the query. Put in some values that will return you some data. Return the data to Excel.

In Data->Import External Data->Parameters... you can set each parameter to a cell of your choice.

Plz tell me if this works for you. Works like a charm for me...

 

Upvote 0

R

Ragnar78

Board Regular

Joined

Feb 10, 2004

Messages

210

• Mar 11, 2004

• #4

Tx alot...i'll be checking these tomorrow, we have DB maintanence....
Anyway i can edit the query and see the parameters...
and value (from what i remember) but still i cannot check the Paramter option..
so ill fix it and see if it works...

thx

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Mar 11, 2004

• #5

Anyway i can edit the query and see the parameters...
and value (from what i remember) but still i cannot check the Paramter option..

Click to expand...

Did you test putting something in the parameter field in brackets?

E.g.,

[Whatever]

 

Upvote 0

R

Ragnar78

Board Regular

Joined

Feb 10, 2004

Messages

210

• Mar 11, 2004

• #6

well when i paste the query...i had already put in an argument in the 'close where'
That argument was (obviosly) shown in the SQL editor...
but i want it to check a value in a cell...and i couldn't get it.

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Mar 11, 2004

• #7

Yes, well, it's imperative that if you want to do what you are asking for, you must change your paramater value to [x] with the brackets in design mode. What goes in between does not matter. Whatever you want, just as long as there are brackets.

Some examples might include:

[Zut Alors!]
[Fruity Pebbles]


It does not matter where, deisgn mode, sql view. Here's the example of sql:

WHERE Blah1 = blah AND Blah2 = blahblah AND blah3=?

But it's easier to do this in design view:

Criteria Field: Field X
Value: [Sacre Bleu]

Return the data, then right-click, Parameters... Use option 3. Voila.

N'est pas?

 

Upvote 0

R

Ragnar78

Board Regular

Joined

Feb 10, 2004

Messages

210

• Mar 12, 2004

• #8

It couldn't have worked better...
Works like a Charm...

Merci

Oh mais que ce passe-t-il?

 

Upvote 0

NateO

Legend

Joined

Feb 17, 2002

Messages

9,700

• Mar 12, 2004

• #9

Ragnar78 said:

It couldn't have worked better...
Works like a Charm...

Merci

Click to expand...

Tres biens! De riens.

Oh mais que ce passe-t-il?

Click to expand...

Pardon moi?

Bon fin de semaine eh.

 

Upvote 0