Multiple paramerter values in one text box

[Praz]

I need to input multiple parameter values in one text box. For example if i
need to list the details for ProductID's 1, 2, 33,48 then these values should
accepted in the Parameter box separated by commas and details should be
listed when i run the query. I tried the Instr function. i.e Param:
Instr([Enter Product ID's seperated by commas],[ProductID]). It works fine
for single digit product IDs but lets say I type in 2,3,45 and run the query.
It will give results for product Id 2,3,4,5, and 45. Whereas I just need 2,3
and 45. Any help appreciated.

 

[Tom van Stiphout]

On Mon, 29 Dec 2008 03:29:01 -0800, Praz

You may also try the IN clause:
select ...
where ProductID IN ([myParameter])
but that won't work either.

I typically create a dynamic SQL statement in this case:
dim sql as string
sql = "select ... " & _
"where ProductID IN (" & myTextControl.Value & ")"
Me.RecordSource = sql

-Tom.
Microsoft Access MVP

 

[Dale Fye]

Or use a multi-select listbox to present the Product IDs, and run your query
in the click event of a command button that concatenates the values from the
listbox into SQL statement.

Personally, I prefer this method, as trying to remember productID values is
a pain in the butt!

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

On Mon, 29 Dec 2008 03:29:01 -0800, Praz

You may also try the IN clause:
select ...
where ProductID IN ([myParameter])
but that won't work either.

I typically create a dynamic SQL statement in this case:
dim sql as string
sql = "select ... " & _
"where ProductID IN (" & myTextControl.Value & ")"
Me.RecordSource = sql

-Tom.
Microsoft Access MVP

I need to input multiple parameter values in one text box. For example if i
need to list the details for ProductID's 1, 2, 33,48 then these values should
accepted in the Parameter box separated by commas and details should be
listed when i run the query. I tried the Instr function. i.e Param:
Instr([Enter Product ID's seperated by commas],[ProductID]). It works fine
for single digit product IDs but lets say I type in 2,3,45 and run the query.
It will give results for product Id 2,3,4,5, and 45. Whereas I just need 2,3
and 45. Any help appreciated.

 

[John Spencer]

Or if you want to continue doing what you are doing try the following.
It is NOT the best way to solve the problem, but it may work for you
while you work out the details of implementing a solution using a
dynamic query based on a listbox.

Instr("," & [Enter Product ID's seperated by commas] & ",", & "," &
[ProductID] & ",")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dale Fye]

Or you could try the technique mentioned by Dirk Goldgar in the thread "Find
ranges or series of integers" in the programming/modulesdaovba newsgroup:

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Or if you want to continue doing what you are doing try the following.
It is NOT the best way to solve the problem, but it may work for you
while you work out the details of implementing a solution using a
dynamic query based on a listbox.

Instr("," & [Enter Product ID's seperated by commas] & ",", & "," &
[ProductID] & ",")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Or use a multi-select listbox to present the Product IDs, and run your query
in the click event of a command button that concatenates the values from the
listbox into SQL statement.

Personally, I prefer this method, as trying to remember productID values is
a pain in the butt!