[ Parameter Queries ] - Multiple Values for One Parameter

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?

Hi Robbie

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

John

"Robbie Baquiran" wrote in message
...
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?

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

"John" wrote in message
...
Hi Robbie

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

John

"Robbie Baquiran" wrote in message
...
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?

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
--

"Robbie Baquiran" wrote in message
...
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

"John" wrote in message
...
Hi Robbie

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

John

"Robbie Baquiran" wrote in message
...
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

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


"Robbie Baquiran" wrote in message
...
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

"John" wrote in message
...
Hi Robbie

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

John

"Robbie Baquiran" wrote in message
...
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?

kludge: ?


"Duane Hookom" wrote in message
...
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
--

"Robbie Baquiran" wrote in message
...
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

"John" wrote in message
...
Hi Robbie

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

John

"Robbie Baquiran" wrote in message
...
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?

You rock Duane, Thanks!

"Duane Hookom" wrote in message
...
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
--

"Robbie Baquiran" wrote in message
...
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

"John" wrote in message
...
Hi Robbie

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

John

"Robbie Baquiran" wrote in message
...
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?

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

"John" wrote in message
...
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


"Robbie Baquiran" wrote in message
...
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

"John" wrote in message
...
Hi Robbie

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

John

"Robbie Baquiran" wrote in message
...
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?

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/O...p#Hookom,Duane.

--
Duane Hookom
MS Access MVP


"Robbie Baquiran" wrote in message
...
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

"John" wrote in message
...
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


"Robbie Baquiran" wrote in message
...
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

"John" wrote in message
...
Hi Robbie

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

John

"Robbie Baquiran" wrote in message
...
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?