DataBase - IN Clause using multi-select form parameter

Asked By thorn2fis

22-Oct-08 06:03 PM

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!

22-Oct-08 06:53 PM

On Wed, 22 Oct 2008 15:03:01 -0700, thorn2fish


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
--

John W. Vinson [MVP]

22-Oct-08 08:58 PM

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

23-Oct-08 07:09 AM

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.


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the

24-Oct-08 12:32 AM

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])));

24-Oct-08 11:39 AM

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([Table]![Field] & " In(" & [Forms]![Formname]![textboxname] &

or, using a prompted parameter:

WHERE (((Eval([Table]![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 "," & [list] & "," 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.



--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the

24-Oct-08 12:19 PM

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!