[ Parameter Queries ] - Multiple Values for One Parameter

[ Parameter Queries ] - Multiple Values for One Parameter

Post by Robbie Baq » Sat, 10 Dec 2005 07:36:11


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?
 
 
 

[ Parameter Queries ] - Multiple Values for One Parameter

Post by John » Sat, 10 Dec 2005 07:40:34

Hi Robbie

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

John

 
 
 

[ Parameter Queries ] - Multiple Values for One Parameter

Post by Robbie Baq » Sat, 10 Dec 2005 08:02:35

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

[ Parameter Queries ] - Multiple Values for One Parameter

Post by Duane Hook » Sat, 10 Dec 2005 08:04:44

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
 
 
 

[ Parameter Queries ] - Multiple Values for One Parameter

Post by John » Sat, 10 Dec 2005 08:09:53

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
 
 
 

[ Parameter Queries ] - Multiple Values for One Parameter

Post by John » Sat, 10 Dec 2005 08:17:42

kludge: ?
 
 
 

[ Parameter Queries ] - Multiple Values for One Parameter

Post by Robbie Baq » Sat, 10 Dec 2005 08:23:24

You rock Duane, Thanks!
 
 
 

[ Parameter Queries ] - Multiple Values for One Parameter

Post by Robbie Baq » Sat, 10 Dec 2005 08:30:04

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

[ Parameter Queries ] - Multiple Values for One Parameter

Post by Duane Hook » Sat, 10 Dec 2005 11:23:20

hy 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


"Robbie Baquiran" < XXXX@XXXXX.COM > wrote in message
news:OBwf$6E$ XXXX@XXXXX.COM ...