[ Parameter Queries ] - Multiple Values for One Parameter

[Robbie Baquiran]

I'm trying to make a query to fetch records from a table based on a
parameter given at run time. It works fine if I only want to run the query
against one value, but how do I enter a parameter so it will several values.

For example if I have a simple table of:
tblExample
| strFirstName | strLastName |

My query would be...
Parameters [valFirstName] value;
select * from tblExample where strFirstName = [valFirstName];

Running the query I would enter "Tom" and it would return all records whose
first name is Tom. If I would like to run the query to return all people
with the first name of Tom and Ted, I couldnt enter "Tom or Ted" because it
would error out.

Any Ideas?

 

[John]

Hi Robbie

Have you put "Tom Or Ted" in the criteria section of your query?
Without the quotes....

John

 

[Robbie Baquiran]

If i do it directly to the query - it would return both Tom(s) and Ted(s).
But doing so would require the end user to enter design mode and manually
alter the critera... which is something i REALLY would like to avoid.

So yes that would work, but I would like to be able to use a parameter to
dynamisize this query

Hi Robbie

Have you put "Tom Or Ted" in the criteria section of your query?
Without the quotes....

John

I'm trying to make a query to fetch records from a table based on a
parameter given at run time. It works fine if I only want to run the
query against one value, but how do I enter a parameter so it will
several values.

For example if I have a simple table of:
tblExample
| strFirstName | strLastName |

My query would be...
Parameters [valFirstName] value;
select * from tblExample where strFirstName = [valFirstName];

Running the query I would enter "Tom" and it would return all records
whose first name is Tom. If I would like to run the query to return all
people with the first name of Tom and Ted, I couldnt enter "Tom or Ted"
because it would error out.

Any Ideas?

 

[Duane Hookom]

Try this kludge:
select *
from tblExample
where Instr("," & [Enter First Names with comma between] & ",", "," &
strFirstName & ",")>0

This should work if a user entered "Tom,Ted,Bob"
--
Duane Hookom
MS Access MVP
--

If i do it directly to the query - it would return both Tom(s) and Ted(s).
But doing so would require the end user to enter design mode and manually
alter the critera... which is something i REALLY would like to avoid.

So yes that would work, but I would like to be able to use a parameter to
dynamisize this query

Hi Robbie

Have you put "Tom Or Ted" in the criteria section of your query?
Without the quotes....

John

I'm trying to make a query to fetch records from a table based on a
parameter given at run time. It works fine if I only want to run the
query against one value, but how do I enter a parameter so it will
several values.

For example if I have a simple table of:
tblExample
| strFirstName | strLastName |

My query would be...
Parameters [valFirstName] value;
select * from tblExample where strFirstName = [valFirstName];

Running the query I would enter "Tom" and it would return all records
whose first name is Tom. If I would like to run the query to return all
people with the first name of Tom and Ted, I couldnt enter "Tom or Ted"
because it would error out.

Any Ideas?

 

[John]

Robbie

Enter within the criteria fied as so. [Enter First Choice] Or [Enter
Second Choice]
You can obviously call the fields whatever you want..

Regards

John

If i do it directly to the query - it would return both Tom(s) and Ted(s).
But doing so would require the end user to enter design mode and manually
alter the critera... which is something i REALLY would like to avoid.

So yes that would work, but I would like to be able to use a parameter to
dynamisize this query

Hi Robbie

Have you put "Tom Or Ted" in the criteria section of your query?
Without the quotes....

John

I'm trying to make a query to fetch records from a table based on a
parameter given at run time. It works fine if I only want to run the
query against one value, but how do I enter a parameter so it will
several values.

For example if I have a simple table of:
tblExample
| strFirstName | strLastName |

My query would be...
Parameters [valFirstName] value;
select * from tblExample where strFirstName = [valFirstName];

Running the query I would enter "Tom" and it would return all records
whose first name is Tom. If I would like to run the query to return all
people with the first name of Tom and Ted, I couldnt enter "Tom or Ted"
because it would error out.

Any Ideas?

 

[John]

kludge: ?

Try this kludge:
select *
from tblExample
where Instr("," & [Enter First Names with comma between] & ",", "," &
strFirstName & ",")>0

This should work if a user entered "Tom,Ted,Bob"
--
Duane Hookom
MS Access MVP
--

If i do it directly to the query - it would return both Tom(s) and
Ted(s). But doing so would require the end user to enter design mode and
manually alter the critera... which is something i REALLY would like to
avoid.

So yes that would work, but I would like to be able to use a parameter to
dynamisize this query

Hi Robbie

Have you put "Tom Or Ted" in the criteria section of your query?
Without the quotes....

John

I'm trying to make a query to fetch records from a table based on a
parameter given at run time. It works fine if I only want to run the
query against one value, but how do I enter a parameter so it will
several values.

For example if I have a simple table of:
tblExample
| strFirstName | strLastName |

My query would be...
Parameters [valFirstName] value;
select * from tblExample where strFirstName = [valFirstName];

Running the query I would enter "Tom" and it would return all records
whose first name is Tom. If I would like to run the query to return
all people with the first name of Tom and Ted, I couldnt enter "Tom or
Ted" because it would error out.

Any Ideas?

 

[Robbie Baquiran]

You rock Duane, Thanks!

Try this kludge:
select *
from tblExample
where Instr("," & [Enter First Names with comma between] & ",", "," &
strFirstName & ",")>0

This should work if a user entered "Tom,Ted,Bob"
--
Duane Hookom
MS Access MVP
--

If i do it directly to the query - it would return both Tom(s) and
Ted(s). But doing so would require the end user to enter design mode and
manually alter the critera... which is something i REALLY would like to
avoid.

So yes that would work, but I would like to be able to use a parameter to
dynamisize this query

Hi Robbie

Have you put "Tom Or Ted" in the criteria section of your query?
Without the quotes....

John

I'm trying to make a query to fetch records from a table based on a
parameter given at run time. It works fine if I only want to run the
query against one value, but how do I enter a parameter so it will
several values.

For example if I have a simple table of:
tblExample
| strFirstName | strLastName |

My query would be...
Parameters [valFirstName] value;
select * from tblExample where strFirstName = [valFirstName];

Running the query I would enter "Tom" and it would return all records
whose first name is Tom. If I would like to run the query to return
all people with the first name of Tom and Ted, I couldnt enter "Tom or
Ted" because it would error out.

Any Ideas?

 

[Robbie Baquiran]

Hi John -

Your approach would work - but that would still limit the query restrictions
by 2 values. If the end user would like to see Tom, Ted, Bob, Billy - The
person would be SOL. The real purpose of this question was to help me
simplify a script I had which needed to fetch result sets from a view. With
Duane's approach, I can loop around a Multi Select list box's selections to
genereate a comma delimited list which would serve as the parameter to
retrieve all records in one call of the view.

Thanks though

Robbie

Enter within the criteria fied as so. [Enter First Choice] Or [Enter
Second Choice]
You can obviously call the fields whatever you want..

Regards

John

If i do it directly to the query - it would return both Tom(s) and
Ted(s). But doing so would require the end user to enter design mode and
manually alter the critera... which is something i REALLY would like to
avoid.

So yes that would work, but I would like to be able to use a parameter to
dynamisize this query

Hi Robbie

Have you put "Tom Or Ted" in the criteria section of your query?
Without the quotes....

John

I'm trying to make a query to fetch records from a table based on a
parameter given at run time. It works fine if I only want to run the
query against one value, but how do I enter a parameter so it will
several values.

For example if I have a simple table of:
tblExample
| strFirstName | strLastName |

My query would be...
Parameters [valFirstName] value;
select * from tblExample where strFirstName = [valFirstName];

Running the query I would enter "Tom" and it would return all records
whose first name is Tom. If I would like to run the query to return
all people with the first name of Tom and Ted, I couldnt enter "Tom or
Ted" because it would error out.

Any Ideas?

 

[Duane Hookom]

Why didn't you say you have a multi-select list box ;-)? If we knew you were
comfortable with code, other methods might work better.

Consider the generic function for this at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

Hi John -

Your approach would work - but that would still limit the query
restrictions by 2 values. If the end user would like to see Tom, Ted,
Bob, Billy - The person would be SOL. The real purpose of this question
was to help me simplify a script I had which needed to fetch result sets
from a view. With Duane's approach, I can loop around a Multi Select list
box's selections to genereate a comma delimited list which would serve as
the parameter to retrieve all records in one call of the view.

Thanks though

Robbie

Enter within the criteria fied as so. [Enter First Choice] Or [Enter
Second Choice]
You can obviously call the fields whatever you want..

Regards

John

If i do it directly to the query - it would return both Tom(s) and
Ted(s). But doing so would require the end user to enter design mode and
manually alter the critera... which is something i REALLY would like to
avoid.

So yes that would work, but I would like to be able to use a parameter
to dynamisize this query

Hi Robbie

Have you put "Tom Or Ted" in the criteria section of your query?
Without the quotes....

John

I'm trying to make a query to fetch records from a table based on a
parameter given at run time. It works fine if I only want to run the
query against one value, but how do I enter a parameter so it will
several values.

For example if I have a simple table of:
tblExample
| strFirstName | strLastName |

My query would be...
Parameters [valFirstName] value;
select * from tblExample where strFirstName = [valFirstName];

Running the query I would enter "Tom" and it would return all records
whose first name is Tom. If I would like to run the query to return
all people with the first name of Tom and Ted, I couldnt enter "Tom or
Ted" because it would error out.

Any Ideas?