Need examples of using the In function in a parameter query with .

[Guest]

I'm creating a database for a user. One of my queries drives off of a
specific field that will have multiple different criteria. Instead of being
prompted multiple times they would rather have one prompt and enter all of
the criteria in this one box. If I do not use a prompt I can get the query
to run using the In function as follows:

In ("09K","10K","11K")

When I try to make this a parameter query and prompt the user I get the
error saying the field will be too long; data truncated. I entered it as
follows:

In ([Enter pensgcs for report, format is "09K","10K","11K"])

In searching the Microsoft site I could not find any examples for a
parameter query that worked like my user has requested.

Has anyone out there done something like this? If so, would you share an
example of how you set up this criteria?

Thanks!

 

[Van T. Dinh]

The list cannot be a Parameter since whatever the user enters will be
considered a a single String and not a list.

You need to use the InStr() function like:

SELECT ....
FROM ...
WHERE IsStr([Enter list here:], [YourField]) > 0

Check Access VB Help on the InStr() function.

 

[Marshall Barton]

I'm creating a database for a user. One of my queries drives off of a
specific field that will have multiple different criteria. Instead of being
prompted multiple times they would rather have one prompt and enter all of
the criteria in this one box. If I do not use a prompt I can get the query
to run using the In function as follows:

In ("09K","10K","11K")

When I try to make this a parameter query and prompt the user I get the
error saying the field will be too long; data truncated. I entered it as
follows:

In ([Enter pensgcs for report, format is "09K","10K","11K"])

In searching the Microsoft site I could not find any examples for a
parameter query that worked like my user has requested.

You can not use a parameter for anything other than a single
value so that approach will not work.

However, you can use a function that takes a parameter
string (such as 09K,10K,11K) and determines if a field's
value is contained in it. In this case, there's a built-in
function for this purpose. I think this will work in you're
case:

InStr([Enter pensgcs, format 09K,10K,11K], thefield) > 0