Developer Forum

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

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

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

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

kludge: ?

You rock Duane, Thanks!

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

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