Using IN( ) function in a query

  • Thread starter Thread starter maki
  • Start date Start date
M

maki

I have a table (tblStores) with store names and inventory

Store Item
A hat
A glove
B glove
D hat
D shirt

I have a table (tblItems) with information on each item in inventory

Item Price
hat 1.00
glove 3.00
shirt 2.50
pen 1.25

I have a form (Form1) with a listbox showing the items in inventory.
For example:

"hat"
"glove"
"shirt"
"pen"

When items are selected from the listbox (multi-select = extended)
they are shown in an unbound text box called txtItems. If the user
selects "hat" and "shirt", the text box (txtItems) ends up showing
'hat','shirt'.
I want to query the tblStores to see which stores have which items on
that list. I tried this:

SELECT tblStores.store, tblStores.item
FROM tblStores
WHERE (((tblStores.item) In ([forms]![Form1]![txtItems])));

It comes up blank

When I change the query to the following it works:

SELECT tblStores.store, tblStores.item
FROM tblStores
WHERE (((tblStores.item) In ('hat','shirt')));

I get a table that shows the following:

store item
A hat
D hat
D shirt

Any idea why the IN( ) function won't work with the field reference
instead of actual data? Any help would be appreciated. Thanks.
 
I have a table (tblStores) with store names and inventory

Store Item
A hat
A glove
B glove
D hat
D shirt

I have a table (tblItems) with information on each item in inventory

Item Price
hat 1.00
glove 3.00
shirt 2.50
pen 1.25

I have a form (Form1) with a listbox showing the items in inventory.
For example:

"hat"
"glove"
"shirt"
"pen"

When items are selected from the listbox (multi-select = extended)
they are shown in an unbound text box called txtItems. If the user
selects "hat" and "shirt", the text box (txtItems) ends up showing
'hat','shirt'.
I want to query the tblStores to see which stores have which items on
that list. I tried this:

SELECT tblStores.store, tblStores.item
FROM tblStores
WHERE (((tblStores.item) In ([forms]![Form1]![txtItems])));

It comes up blank

When I change the query to the following it works:

SELECT tblStores.store, tblStores.item
FROM tblStores
WHERE (((tblStores.item) In ('hat','shirt')));

I get a table that shows the following:

store item
A hat
D hat
D shirt

Any idea why the IN( ) function won't work with the field reference
instead of actual data? Any help would be appreciated. Thanks.
Click to expand...

A listbox - even a multiselect listbox - has only one value. You'll need to
write VBA code to loop through the listbox's ItemsSelected collection and
construct the SQL string of your query; it cannot be done using a Paramter.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
A listbox - even a multiselect listbox - has only one value.
Click to expand...

Actually, a multiselect listbox returns Null even when one or more
values are selected, no? You have to use the ItemsSelected
collection and ItemData to get the values out of it. I always have
to look it up every time I need to do this!
 
A listbox - even a multiselect listbox - has only one value. You'll need to
write VBA code to loop through the listbox's ItemsSelected collection and
construct the SQL string of your query; it cannot be done using a Paramter.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you John. I do loop through the selected values to populate the
"txtItems" field. A typical result, as in the example I gave, would
be txtItems = 'hat','shirt'. The only problem I have is running a
query on that field. I want (?) to use the IN() function and find
records that match a value in the expression. For example, IN
('hat','shirt') is in the Criteria row of the query. If I use the
actual text in the query, it works; however if I use the field name to
reference ([forms]![Form1]![txtItems]), it doesn't.

Are you suggesting that the query use something like "IN (For each
varItem in lstItems ..... NEXT....)" where the code that loops through
the selected items is inside the IN statement?
 
A listbox - even a multiselect listbox - has only one value. You'll need to
write VBA code to loop through the listbox's ItemsSelected collection and
construct the SQL string of your query; it cannot be done using a Paramter.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you John. I do loop through the selected values to populate the
"txtItems" field. A typical result, as in the example I gave, would
be txtItems = 'hat','shirt'. The only problem I have is running a
query on that field. I want (?) to use the IN() function and find
records that match a value in the expression. For example, IN
('hat','shirt') is in the Criteria row of the query. If I use the
actual text in the query, it works; however if I use the field name to
reference ([forms]![Form1]![txtItems]), it doesn't.

Are you suggesting that the query use something like "IN (For each
varItem in lstItems ..... NEXT....)" where the code that loops through
the selected items is inside the IN statement?
Click to expand...

You cannot pass a list of multiple items as a parameter - only a single item.
You will NOT be able to use a paramter query for this, either using the
listbox itself, nor using your string of values. You must instead actually
construct the entire SQL of the query in code, and use it as the recordsource
for your report or form.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
maki said:
SELECT tblStores.store, tblStores.item
FROM tblStores
WHERE (((tblStores.item) In ([forms]![Form1]![txtItems])));
Click to expand...

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.
 
Thank you all for your input. I am trying out all of the
suggestions. Haven't found any that work the way I was thinking of
yet, but it may be that my thinking is flawed. I will plug along and,
if nothing else, probably learn a lot from the experience.
 
maki said:
Thank you all for your input. I am trying out all of the
suggestions. Haven't found any that work the way I was thinking of
yet, but it may be that my thinking is flawed. I will plug along and,
if nothing else, probably learn a lot from the experience.
Click to expand...

Let's spell it out then.
1. In VBA code, loop through the selected items, creating a
comma-delimited string containing the items.
dim sItems As string, sSQL As string
sItems = "'selection1',...,'selectionN'"

then concatenate that string with a string containing the rest of the
sql statement"

sSQL="select blah, blah, blah from tablename " & _
"where fieldname in (" & sItems & ")"
debug.print sSQL

Then set a querydef's SQL property to that string, or simply open a
recordset on it ...
 
Back
Top