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

Microsoft Query parameter - "is one of"
 

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

Microsoft Query parameter - "is one of"
 

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

Microsoft Query parameter - "is one of"
 

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

Microsoft Query parameter - "is one of"
 

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

Microsoft Query parameter - "is one of"
 

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