Parameter query with AND or OR

[Emmweb]

I have a parameter query which prompts for semester. It works perfectly as
long as you are only looking for a single term, Spring 2005, for instance.
However, if the user wants all records for 2 terms, or 3, for example, is
there any way for the user to use an AND or OR with quotes or something to
make this work? I've tried several variations and haven't stumbled on the
right thing yet...

Thanks!
Emma

 

[Ken Snell [MVP]]

You might be able to do this for an OR logic:

SELECT * FROM TableName
WHERE Semester IN
([Enter the term(s) (separate multiple terms with a comma):]);


Thus, you'd enter
Spring 2005, Spring 2004, Spring 2003
if you wanted to search for either of the three terms.

This approach will not provide an AND logic. You'd need to use multiple
parameter requests, which isn't good for your user, or else use a form to
allow the user to enter the filtering criteria and then have your query read
from the form for the values in the criteria (or better yet, let the form
create the query via VBA code).

 

[Emmweb]

Ken -

Thanks so much for your help. However, I'm still having some trouble. When I
insert semesters directly into the SQL inside IN(), separated by commas, it
works just fine. But if I use a parameter, for some reason it dies. I can put
the exact same thing within the parameter that I put in the IN() in SQL, but
no luck.

If it helps, my SQL reads:
SELECT SSN, Semester
FROM [001-051]
WHERE ((([Record Type])="UW Enr") AND ((Semester) In ([Semester:])));

Thanks again,
Emma

You might be able to do this for an OR logic:

SELECT * FROM TableName
WHERE Semester IN
([Enter the term(s) (separate multiple terms with a comma):]);


Thus, you'd enter
Spring 2005, Spring 2004, Spring 2003
if you wanted to search for either of the three terms.

This approach will not provide an AND logic. You'd need to use multiple
parameter requests, which isn't good for your user, or else use a form to
allow the user to enter the filtering criteria and then have your query read
from the form for the values in the criteria (or better yet, let the form
create the query via VBA code).

--

Ken Snell
<MS ACCESS MVP>

I have a parameter query which prompts for semester. It works perfectly as
long as you are only looking for a single term, Spring 2005, for instance.
However, if the user wants all records for 2 terms, or 3, for example, is
there any way for the user to use an AND or OR with quotes or something to
make this work? I've tried several variations and haven't stumbled on the
right thing yet...

Thanks!
Emma

 

[david epsom dot com dot au]

your parameter has been converted to a single string:

((semester) IN ("1,2,3"))

I don't remember how you should do this, but here is another
method that might work:

WHERE ..... AND (instr([pSemester],[Semester]))

which when given "1,2,3" would expand to

WHERE ..... AND (instr("1,2,3",[Semester]))


(david)

Ken -

Thanks so much for your help. However, I'm still having some trouble. When
I
insert semesters directly into the SQL inside IN(), separated by commas,
it
works just fine. But if I use a parameter, for some reason it dies. I can
put
the exact same thing within the parameter that I put in the IN() in SQL,
but
no luck.

If it helps, my SQL reads:
SELECT SSN, Semester
FROM [001-051]
WHERE ((([Record Type])="UW Enr") AND ((Semester) In ([Semester:])));

Thanks again,
Emma

You might be able to do this for an OR logic:

SELECT * FROM TableName
WHERE Semester IN
([Enter the term(s) (separate multiple terms with a comma):]);


Thus, you'd enter
Spring 2005, Spring 2004, Spring 2003
if you wanted to search for either of the three terms.

This approach will not provide an AND logic. You'd need to use multiple
parameter requests, which isn't good for your user, or else use a form to
allow the user to enter the filtering criteria and then have your query
read
from the form for the values in the criteria (or better yet, let the form
create the query via VBA code).

--

Ken Snell
<MS ACCESS MVP>

I have a parameter query which prompts for semester. It works perfectly
as
long as you are only looking for a single term, Spring 2005, for
instance.
However, if the user wants all records for 2 terms, or 3, for example,
is
there any way for the user to use an AND or OR with quotes or something
to
make this work? I've tried several variations and haven't stumbled on
the
right thing yet...

Thanks!
Emma

 

[Ken Snell [MVP]]

Thanks, david, I had forgotten that part of the "trick":

SELECT * FROM TableName
WHERE InStr([Enter the term(s) (separate multiple terms with a comma):],
[TermFieldName])>0;

--

Ken Snell
<MS ACCESS MVP>

your parameter has been converted to a single string:

((semester) IN ("1,2,3"))

I don't remember how you should do this, but here is another
method that might work:

WHERE ..... AND (instr([pSemester],[Semester]))

which when given "1,2,3" would expand to

WHERE ..... AND (instr("1,2,3",[Semester]))


(david)

Ken -

Thanks so much for your help. However, I'm still having some trouble.
When I
insert semesters directly into the SQL inside IN(), separated by commas,
it
works just fine. But if I use a parameter, for some reason it dies. I can
put
the exact same thing within the parameter that I put in the IN() in SQL,
but
no luck.

If it helps, my SQL reads:
SELECT SSN, Semester
FROM [001-051]
WHERE ((([Record Type])="UW Enr") AND ((Semester) In ([Semester:])));

Thanks again,
Emma

You might be able to do this for an OR logic:

SELECT * FROM TableName
WHERE Semester IN
([Enter the term(s) (separate multiple terms with a comma):]);


Thus, you'd enter
Spring 2005, Spring 2004, Spring 2003
if you wanted to search for either of the three terms.

This approach will not provide an AND logic. You'd need to use multiple
parameter requests, which isn't good for your user, or else use a form
to
allow the user to enter the filtering criteria and then have your query
read
from the form for the values in the criteria (or better yet, let the
form
create the query via VBA code).

--

Ken Snell
<MS ACCESS MVP>


I have a parameter query which prompts for semester. It works perfectly
as
long as you are only looking for a single term, Spring 2005, for
instance.
However, if the user wants all records for 2 terms, or 3, for example,
is
there any way for the user to use an AND or OR with quotes or
something to
make this work? I've tried several variations and haven't stumbled on
the
right thing yet...

Thanks!
Emma