Use of "In" in parameter queries

[Fred Zack]

Basically In does not seem to work in a paramter query. I would like to have
a parameter that asks the user to input a list of subject numbers to
analyze. I tried using In , which works fine for regular criteria, but it
will only work if you only input one number. Can't parameter criteria take
more than one value like a list.?

A numerical range or "Between" won't work as the list could be any
combination of the numbers - 1,6,11,16,21,26,31,36,41,46,51.

 

[KARL DEWEY]

You can do something like this ---
SELECT [Change Requests].x
FROM [Change Requests]
WHERE ((([Enter search list]) Like "*" & [x] & "*"));

It has a bad side in that if you enter 1 3 4 then it will find 1, 11,
12, 13,..3, 31, 32, 33,...4, 41, 42, 43,...

 

[Jamie Collins]

Can't parameter criteria take
more than one value like a list.?

A parameter query can take more than one parameter with an option
value (e.g. NULL). See:

http://groups.google.com/group/microsoft.public.access.queries/msg/8e2f82b4d94236a2

Jet SQL has no 'array' data types. Access 2007 introduced multivalue
data types but I can't see that the Access team introduced the
necessary predicates to handle them in a parameter query as you
require.

Jamie.

--

 

[UpRider]

Fred, add a column to your query for an expression:
myParam:InStr([Enter values separated by commas],[subjectnum])
for the criteria, enter >0 and uncheck the Show box.
Bingo

UpRider

 

[Proposal Doctor]

Karl,

Thanks for posting this reply in 2007. I have been looking for an hour for
a way to use a parameter query with a multi value field. Within a couple
minutes, I was able to modify your SQL voila:

.. . .
WHERE (((tblPeople.president.Value) Like "*" & [Which president?] & "*"));

Thanks.

David

You can do something like this ---
SELECT [Change Requests].x
FROM [Change Requests]
WHERE ((([Enter search list]) Like "*" & [x] & "*"));

It has a bad side in that if you enter 1 3 4 then it will find 1, 11,
12, 13,..3, 31, 32, 33,...4, 41, 42, 43,...
--
KARL DEWEY
Build a little - Test a little

Basically In does not seem to work in a paramter query. I would like to have
a parameter that asks the user to input a list of subject numbers to
analyze. I tried using In , which works fine for regular criteria, but it
will only work if you only input one number. Can't parameter criteria take
more than one value like a list.?

A numerical range or "Between" won't work as the list could be any
combination of the numbers - 1,6,11,16,21,26,31,36,41,46,51.