|
|
Hi, I have a table in MS access as the external data source. I want to
add a parameter value, so I Edit the query and add [?] in the value
field. When I run the query it does not prompt me for a value.
However, when I put a valid value straight into the query (where I put
the question mark), it does produce the required result, hence the
query itself should be okay.
Ultimately, I want to create a parameter query by using a cell in the
worksheet. As per Office Help, I need to click on the Parameter Icon
on the External Data toolbar to specify the value. This button is
disabled. What can I do to make this all work? I'm at my wit's end
(though that's not a long journey). I'm using Ms Office 2003.
Thanks, Paul
|
|
paul wrote:
[Quoted Text] > Hi, I have a table in MS access as the external data source. I want to
> add a parameter value, so I Edit the query and add [?] in the value
> field. When I run the query it does not prompt me for a value.
> However, when I put a valid value straight into the query (where I put
> the question mark), it does produce the required result, hence the
> query itself should be okay.
> Ultimately, I want to create a parameter query by using a cell in the
> worksheet. As per Office Help, I need to click on the Parameter Icon
> on the External Data toolbar to specify the value. This button is
> disabled. What can I do to make this all work? I'm at my wit's end
> (though that's not a long journey). I'm using Ms Office 2003.
Hi Paul,
This works for me in 2003:
http://www.dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/
Beyond that, try something more than ? between the brackets?
|
|
hi
for some reason, mrcrosoft query will not let you edit a parameter query
until you have a parameter query. this is why your parameter icons don't
light up.
so create the parameter query. how.
if you have not created your query, create on through the wizard. (i think
MS puts more into the wizard that needs to be). if your have created your
query, right click the query data area and click edit query. click throuh the
wizard dialogs to the the last dialog, the finish dialog. check "view data or
edit qfufery in microsof query"
click finish.
when the MSQ dialog comes up, on the tool bar, click critera>add critera.
when the add criteria dialog comes up, add some critera.
this will be something = somthing. this will produce a critera pane similar
to access with the criteria listed.
click into the value field and replace the criteria you just entered
with...[What]
you now have a parameter query.
a query with critera will return data with pre-defined critera.
a parameter query will prompt the user to input criteria.
this should light up your parameter icons.
click an icon. you should get a parameter dialog that lets you state where
the parameter is coming from.
1. Prompt for value using the following string:
2. use the following value:
3. get the value from the following cell:
select the one you wish...enter your value in the appropreate text box.
seems like the long way around and i agree. i've been bitching about this
since xl97.
regards
FSt1
"paul" wrote:
[Quoted Text] > Hi, I have a table in MS access as the external data source. I want to
> add a parameter value, so I Edit the query and add [?] in the value
> field. When I run the query it does not prompt me for a value.
> However, when I put a valid value straight into the query (where I put
> the question mark), it does produce the required result, hence the
> query itself should be okay.
> Ultimately, I want to create a parameter query by using a cell in the
> worksheet. As per Office Help, I need to click on the Parameter Icon
> on the External Data toolbar to specify the value. This button is
> disabled. What can I do to make this all work? I'm at my wit's end
> (though that's not a long journey). I'm using Ms Office 2003.
> Thanks, Paul
>
>
|
|
On Dec 27, 5:32 am, FSt1 <F...[ at ]discussions.microsoft.com> wrote:
[Quoted Text] > hi
> for some reason, mrcrosoft query will not let you edit a parameter query
> until you have a parameter query. this is why your parameter icons don't
> light up.
> so create the parameter query. how.
> if you have not created your query, create on through the wizard. (i think
> MS puts more into the wizard that needs to be). if your have created your
> query, right click the query data area and click edit query. click throuh the
> wizard dialogs to the the last dialog, the finish dialog. check "view data or
> edit qfufery in microsof query"
> click finish.
> when the MSQ dialog comes up, on the tool bar, click critera>add critera.
> when the add criteria dialog comes up, add some critera.
> this will be something = somthing. this will produce a critera pane similar
> to access with the criteria listed.
> click into the value field and replace the criteria you just entered
> with...[What]
> you now have a parameter query.
> a query with critera will return data with pre-defined critera.
> a parameter query will prompt the user to input criteria.
> this should light up your parameter icons.
> click an icon. you should get a parameter dialog that lets you state where
> the parameter is coming from.
> 1. Prompt for value using the following string:
> 2. use the following value:
> 3. get the value from the following cell:
> select the one you wish...enter your value in the appropreate text box..
>
> seems like the long way around and i agree. i've been bitching about this
> since xl97.
>
> regards
> FSt1
>
>
>
> "paul" wrote:
> > Hi, I have a table in MS access as the external data source. I want to
> > add a parameter value, so I Edit the query and add [?] in the value
> > field. When I run the query it does not prompt me for a value.
> > However, when I put a valid value straight into the query (where I put
> > the question mark), it does produce the required result, hence the
> > query itself should be okay.
> > Ultimately, I want to create a parameter query by using a cell in the
> > worksheet. As per Office Help, I need to click on the Parameter Icon
> > on the External Data toolbar to specify the value. This button is
> > disabled. What can I do to make this all work? I'm at my wit's end
> > (though that's not a long journey). I'm using Ms Office 2003.
> > Thanks, Paul- Hide quoted text -
>
> - Show quoted text -
Hi FSt1, this is excellent, thanks. I followed your instructions to
the letter and the parameter query indeed lit up. It's very curious to
me why one needs to follow these exact steps (moreover, how did you
discover this route anyway?) I too created a parameter query first,
'coz you could make a point that you can only edit something that
actually exists. However, I did not go through all the buttons that
you pointed out. I manually edited the query and, apparently, that
does not work. Thanks again, I can now create automatic and
interactive reports, which will help me a lot in simplifying our
processes. Have a wonderful New Year, Paul
|
|
On Dec 27, 4:11 am, smartin <smartin...[ at ]gmail.com> wrote:
[Quoted Text] > paul wrote: > > Hi, I have a table in MS access as the external data source. I want to > > add a parameter value, so I Edit the query and add [?] in the value > > field. When I run the query it does not prompt me for a value. > > However, when I put a valid value straight into the query (where I put > > the question mark), it does produce the required result, hence the > > query itself should be okay. > > Ultimately, I want to create a parameter query by using a cell in the > > worksheet. As per Office Help, I need to click on the Parameter Icon > > on the External Data toolbar to specify the value. This button is > > disabled. What can I do to make this all work? I'm at my wit's end > > (though that's not a long journey). I'm using Ms Office 2003. > > Hi Paul, > > This works for me in 2003: > > http://www.dailydoseofexcel.com/archives/2004/12/13/parameters-in-exc...> > Beyond that, try something more than ? between the brackets?
Hi SMartin, thanks for your reply. I did try your suggestion, but the
parameter button in your linked example was not clickable/refused to
lit up. I turned to the solution of FSt1 and that did the trick.
Nevertheless, I appreciate your effort. Hapy New Year, Paul
|
|
|