Query with a mult-value criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

They say that persistence pays off!

I have tried asking this question before AND I have seen posts from others
asking what I believe is the same question, but neither I nor other people
have received a satisfactory answer. So let me try again…..

I have a single table data base. I want to run a query with a “criteriaâ€
that will accept multiple values. For example: [Enter state] where the user
can enter multiple states.

I don’t know how else to ask the question.
 
Senator said:
They say that persistence pays off!

I have tried asking this question before AND I have seen posts from
others asking what I believe is the same question, but neither I nor
other people have received a satisfactory answer. So let me try
again...

I have a single table data base. I want to run a query with a
"criteria" that will accept multiple values. For example: [Enter
state] where the user can enter multiple states.

I don't know how else to ask the question.
Click to expand...

SELECT *
FROM SomeTable
WHERE InStr([Enter State(s)], [FieldName]) > 0

This is the only way I know of to do this with the simple self-prompting
parameter feature in an Access query, but it is not an efficient query since
it is filtering on an expression thus making a full table-scan required.

It would be better to provide a form where the users enter the states and
then have code that builds the WHERE clause for the query and applies it
using ORs or an IN() clause.
 
You could set up parameters in the criteria i.e.

[Enter State 1]
[Enter State 2 or leave blank]
[Enter State 3 or leave blank]

and so on, you would have to know the maximum likely criteria, any that are
left blank don't return anything so should work. Otherwise you will have to
look at list boxes which are much more complicated

Hope this helps

Sheila
 
They say that persistence pays off!

I have tried asking this question before AND I have seen posts from others
asking what I believe is the same question, but neither I nor other people
have received a satisfactory answer. So let me try again…..

I have a single table data base. I want to run a query with a “criteria”
that will accept multiple values. For example: [Enter state] where the user
can enter multiple states.

I don’t know how else to ask the question.
Click to expand...

The only way (other than what Rick and Sheila have suggested) is to
solicit the criteria using a Form rather than a prompt, and use VBA
code to build a SQL string.

Access parameter queries are not capable of doing what you ask by
themselves. It's not that nobody's answered the question; it's that
there IS NO ANSWER because you simply cannot do it the way you're
asking. If that answer is not satisfactory, I'm sorry; you'll need to
take it up with Microsoft, because that's simply the facts.

John W. Vinson[MVP]
 
Back
Top