Microsoft Query parameter - "is one of"

I have a query in Excel that uses a parameter in a cell. Previously I just
had an "equal to" operator in Microsoft Query which worked fine. For example
the field Unit should be equal to the parameter value. Nothing strange.

However, now I want to use an "in" (or "is one of") operator instead and
send in a string to put within the "in" statement. So I have IN([parameter])
in the query. This works fine when I send in just one value, like "A", but if
I try to use several, like "A,B,C", it doesn't work. Does anyone have an idea
for how I should pass a parameter with several choices for the "IN" operator?
I don't want to create several parameters and OR statements in the query
because I want it to be dynamic in number of choices. Thanks in advance!

/Marcus

Hi,

I haven't much experience with Microsoft Query, but with SQL. I didn't know
that you could use cells as parameters in MS Query, how is it done?
Regarding your question, when the field is text the IN clause in SQL you
must identify each option as text, so you may try to pass "A","B","C" instead
of just "A,B,C".

Miguel.

"Marcus Langell" wrote:

> I have a query in Excel that uses a parameter in a cell. Previously I just
> had an "equal to" operator in Microsoft Query which worked fine. For example
> the field Unit should be equal to the parameter value. Nothing strange.
>
> However, now I want to use an "in" (or "is one of") operator instead and
> send in a string to put within the "in" statement. So I have IN([parameter])
> in the query. This works fine when I send in just one value, like "A", but if
> I try to use several, like "A,B,C", it doesn't work. Does anyone have an idea
> for how I should pass a parameter with several choices for the "IN" operator?
> I don't want to create several parameters and OR statements in the query
> because I want it to be dynamic in number of choices. Thanks in advance!
>
> /Marcus

When you use parameter queries in MS Query, it treats the value in the parameter as a single text string, therefore if you have a condition of In([Parameter1]), if you enter a value of A,B,C or 'A','B','C' it tries to retrieve records which equal A,B,C or 'A','B','C' and not as you want.

The only thing I can suggest is setup multiple parameters and use OR to relate them to each other, and then use a combo box, to select the options, have a formula which extracts each option into individual cells. When these cell values change, get the query to run by selecting option 3 on the parameter icon within excel.

This probably wasn't what you wanted to hear, but having used MSQuery and parameters a lot, this is the only solution I have found to date.

I may have stumbled onto something you can use.

Here's my setup

Database: Excel File
Table: An Excel Range named rngDateString
Field_1: MyDates (various dates)
Field_2: MyStrings (various letters a, b, c, d, etc)

QueryFields:
MyDates
MyStrings

The Criteria:
1)Field:MyStrings
Crit_1:<>[ParamValues]

2)Field: Not 0
Crit_2: InStr(1,[ParamValues],[MyString])

When I ran the query, for [ParamValues] i entered ABC.

When I returned the data to Excel, I pointed the Parameter to a cell
containing a string of letters I wanted to match: a_b_c

Excel accepted the query, only returned records where MyStrings matched a or
b or c. When I changed the cell contents to d_e and refreshed the query, the
correct records displayed.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Marcus Langell" wrote:

> I have a query in Excel that uses a parameter in a cell. Previously I just
> had an "equal to" operator in Microsoft Query which worked fine. For example
> the field Unit should be equal to the parameter value. Nothing strange.
>
> However, now I want to use an "in" (or "is one of") operator instead and
> send in a string to put within the "in" statement. So I have IN([parameter])
> in the query. This works fine when I send in just one value, like "A", but if
> I try to use several, like "A,B,C", it doesn't work. Does anyone have an idea
> for how I should pass a parameter with several choices for the "IN" operator?
> I don't want to create several parameters and OR statements in the query
> because I want it to be dynamic in number of choices. Thanks in advance!
>
> /Marcus

Thanks for that suggestion, Ron! That's very interesting and it works fine
the way you explained it (when reading from an Excel file). But it doesn't
work when reading from an SQL database. Anyone know why?
Is there any good documentation of Microsoft Query somewhere on the web?

/Marcus


"Ron Coderre" wrote:

> I may have stumbled onto something you can use.
>
> Here's my setup
>
> Database: Excel File
> Table: An Excel Range named rngDateString
> Field_1: MyDates (various dates)
> Field_2: MyStrings (various letters a, b, c, d, etc)
>
> QueryFields:
> MyDates
> MyStrings
>
> The Criteria:
> 1)Field:MyStrings
> Crit_1:<>[ParamValues]
>
> 2)Field: Not 0
> Crit_2: InStr(1,[ParamValues],[MyString])
>
> When I ran the query, for [ParamValues] i entered ABC.
>
> When I returned the data to Excel, I pointed the Parameter to a cell
> containing a string of letters I wanted to match: a_b_c
>
> Excel accepted the query, only returned records where MyStrings matched a or
> b or c. When I changed the cell contents to d_e and refreshed the query, the
> correct records displayed.
>
> Is that something you can work with?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Marcus Langell" wrote:
>
> > I have a query in Excel that uses a parameter in a cell. Previously I just
> > had an "equal to" operator in Microsoft Query which worked fine. For example
> > the field Unit should be equal to the parameter value. Nothing strange.
> >
> > However, now I want to use an "in" (or "is one of") operator instead and
> > send in a string to put within the "in" statement. So I have IN([parameter])
> > in the query. This works fine when I send in just one value, like "A", but if
> > I try to use several, like "A,B,C", it doesn't work. Does anyone have an idea
> > for how I should pass a parameter with several choices for the "IN" operator?
> > I don't want to create several parameters and OR statements in the query
> > because I want it to be dynamic in number of choices. Thanks in advance!
> >
> > /Marcus

OK, thanks!
That brings up another question though; is it possible to programmatically
set the parameter ranges for a query?

/Marcus

"Gary Brown" wrote:

>
> When you use parameter queries in MS Query, it treats the value in the
> parameter as a single text string, therefore if you have a condition of
> In([Parameter1]), if you enter a value of A,B,C or 'A','B','C' it tries
> to retrieve records which equal A,B,C or 'A','B','C' and not as you
> want.
>
> The only thing I can suggest is setup multiple parameters and use OR
> to relate them to each other, and then use a combo box, to select the
> options, have a formula which extracts each option into individual
> cells. When these cell values change, get the query to run by selecting
> option 3 on the parameter icon within excel.
>
> This probably wasn't what you wanted to hear, but having used MSQuery
> and parameters a lot, this is the only solution I have found to date.
>
>
> --
> Gary Brown
> ------------------------------------------------------------------------
> Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
> View this thread: http://www.excelforum.com/showthread...hreadid=535886
>
>

I have been able to make this work quite easily... easier than using the instr

if you are using the MS Query graphical editor
Click on Criteria
Field select the one you want to find in the list
Operator: select is one of
Value: enter [Q1],[Q2],[Q3],[Q4] one entry for the number of elements you may have in your list... it still works if some of those are empty

use the parameter definition to set the cell location for each of the parameters.


if you are using excel..
enter "Where field in (?,?,?,?,?)"