IN Clause using multi-select form parameter

[thorn2fish]

I am having a problem where I get empty query results using a multi-select
listbox as a parameter to a query. I have coded a MS Access form to feed the
multi-select entries as a parameter to a MS Access Query Object. The two
objects on the form are the multi-select listbox and a button to execute the
query. Yes, I am leaving the form open.

Here is the query "Query2":
SELECT Table1.System, Table1.Size
FROM Table1
WHERE Table1.System In ([Forms]![Form1]![System]);

Here is the EventClick expression from the button on the form:
Private Sub Command3_Click()
DoCmd.OpenQuery "Query2"
End Sub

Where "System" is the name of the multi-value list box from "Form1".

I thought this mirrored the example I saw, but it isn't working. Does
anyone see the obvious?
Thanks!

 

[John W. Vinson]

I am having a problem where I get empty query results using a multi-select
listbox as a parameter to a query. I have coded a MS Access form to feed the
multi-select entries as a parameter to a MS Access Query Object. The two
objects on the form are the multi-select listbox and a button to execute the
query. Yes, I am leaving the form open.

Here is the query "Query2":
SELECT Table1.System, Table1.Size
FROM Table1
WHERE Table1.System In ([Forms]![Form1]![System]);

Here is the EventClick expression from the button on the form:
Private Sub Command3_Click()
DoCmd.OpenQuery "Query2"
End Sub

Where "System" is the name of the multi-value list box from "Form1".

I thought this mirrored the example I saw, but it isn't working. Does
anyone see the obvious?
Thanks!

A listbox - multiselect or not - has only *one* value. To construct a query
with multiple values you need to actually build a SQL string in code. For
sample code see
http://www.mvps.org/access/forms/frm0007.htm

 

[NetworkTrade]

that puzzles me....have never had a need for a listbox to be optioned as
multi select; if there is only one value - which value is it? .... and ...
why make it able to be multi select if it can only be single value??

--
NTC

I am having a problem where I get empty query results using a multi-select
listbox as a parameter to a query. I have coded a MS Access form to feed the
multi-select entries as a parameter to a MS Access Query Object. The two
objects on the form are the multi-select listbox and a button to execute the
query. Yes, I am leaving the form open.

Here is the query "Query2":
SELECT Table1.System, Table1.Size
FROM Table1
WHERE Table1.System In ([Forms]![Form1]![System]);

Here is the EventClick expression from the button on the form:
Private Sub Command3_Click()
DoCmd.OpenQuery "Query2"
End Sub

Where "System" is the name of the multi-value list box from "Form1".

I thought this mirrored the example I saw, but it isn't working. Does
anyone see the obvious?
Thanks!

A listbox - multiselect or not - has only *one* value. To construct a query
with multiple values you need to actually build a SQL string in code. For
sample code see
http://www.mvps.org/access/forms/frm0007.htm

 

[Bob Barrows [MVP]]

The Value property can only contain a single value. I believe it will
contain the last value that was selected. In order to get all the selected
values, you have to loop through the selecteditems collection.

that puzzles me....have never had a need for a listbox to be optioned
as
multi select; if there is only one value - which value is it? ....
and ... why make it able to be multi select if it can only be single
value??

I am having a problem where I get empty query results using a
multi-select listbox as a parameter to a query. I have coded a MS
Access form to feed the multi-select entries as a parameter to a MS
Access Query Object. The two objects on the form are the
multi-select listbox and a button to execute the query. Yes, I am
leaving the form open.

Here is the query "Query2":
SELECT Table1.System, Table1.Size
FROM Table1
WHERE Table1.System In ([Forms]![Form1]![System]);

Here is the EventClick expression from the button on the form:
Private Sub Command3_Click()
DoCmd.OpenQuery "Query2"
End Sub

Where "System" is the name of the multi-value list box from "Form1".

I thought this mirrored the example I saw, but it isn't working.
Does anyone see the obvious?
Thanks!

A listbox - multiselect or not - has only *one* value. To construct
a query with multiple values you need to actually build a SQL string
in code. For sample code see
http://www.mvps.org/access/forms/frm0007.htm

 

[thorn2fish]

OK I came across another example showed how to step through the selected
items and concatenate them into a string. It stated that this string could
be used by the query, but it didn't state whether it was talking about SQL
built in the VBA code or a Query object. Building the string is no problem,
so just to demonstrate the process, I just passed a string (through the form)
to the query. Still no luck. Should this work?

Button event VBA:
Private Sub Command3_Click()
Forms!Form2!SelSys = "MST1,SYSA"
DoCmd.OpenQuery "Query3"
End Sub

Query Object SQL:
SELECT Table1.System, Table1.Size
FROM Table1
WHERE (((Table1.System) In ([Forms]![Form2]![SelSys])));

 

[Bob Barrows [MVP]]

OK I came across another example showed how to step through the
selected items and concatenate them into a string. It stated that
this string could be used by the query, but it didn't state whether
it was talking about SQL built in the VBA code or a Query object.
Building the string is no problem, so just to demonstrate the
process, I just passed a string (through the form) to the query.
Still no luck. Should this work?

Button event VBA:
Private Sub Command3_Click()
Forms!Form2!SelSys = "MST1,SYSA"
DoCmd.OpenQuery "Query3"
End Sub

Query Object SQL:
SELECT Table1.System, Table1.Size
FROM Table1
WHERE (((Table1.System) In ([Forms]![Form2]![SelSys])));

Absolutely not. The IN() clause expects a list of values, not a string
containing a comma-delimited list of values. In your attempt, you are
passing a single string to the query engine, not a list of multiple strings.

Here is a compilation of posts about how to deal with this issue:
There are two solutions for this problem listed in the following KB article
(Q210530 - ACC2000: How to Create a Parameter In() Statement), found by
searching for the keywords "parameter list query" (no quotes) at
http://support.microsoft.com.

http://support.microsoft.com/support/kb/articles/Q210/5/30.ASP

The first solution uses Instr() to test the field values against the list in
the parameter. The second involves dynamically creating a SQL statement in
code.

Thanks to Paul Overway, here is a third solution, using the Eval function:

WHERE (((Eval(

![Field] & " In(" & [Forms]![Formname]![textboxname] &
")"))=True))

or, using a prompted parameter:

WHERE (((Eval(

![Field] & " In(" & [Enter List] & ")"))=True))


Thanks to Jeffrey A. Williams, here's a 4th solution:

If you don't mind adding a table to your database, and you're comfortable
dealing with possible multi-user issues, this will perform better than
either of the solutions that involve running a function (Instr or Eval) on
every row of your table:

Create a new table with two fields:

tblCriteria:
Criteria text
Selected boolean (yes/no)

Populate the table with your values and select a couple of items. Now you
can use this table in your query as such:

Select * from table1
inner join tblcriteria
on table1.[your criteria field] = tblcriteria.criteria
where tblcriteria.selected = true

You can easily setup a form (or subform) that is bound to tblCriteria and
allow the users the
ability of selecting which values they want.



Thanks to Michael Walsh, here's yet another way:

SELECT Table3.ConName, Table3.State, Table3.Zip
FROM Table3
WHERE "," &

• & "," LIKE "*," & [ConName] & ",*"
  
  with [param] some string like: '1,4,5,7'
  
  note that there is no space after the comas.
  
  
  It works simply. If AccountID is 45, clearly ',1,4,5,7,' LIKE
  '*,45,*' returns false.
  If AccountID is 4, on the other hand, ',1,4,5,7,' LIKE '*,4,*'
  returns true.
  
  So, you have, in effect, an IN( ) where the list is a parameter.

 

[thorn2fish]

Bob, Thanks for the direct answer to the question and for the detailed
description of the options. I hope others will benefit.

I added a new post "Multi-Selection Code Review" in "Forms Coding" that
contains my solution. It isn't as simple as I would like, but it works and I
think it contains all the essentials. I'll be reviewing it in light of your
input as well.

Thanks again!