get parameter inside IN() clause

[sagirodin]

Hi,

Im using access 2003 and what i wanted to do is get a parameter inside a query like this

"SELECT * FROM tbl_users WHERE user_id IN ([])"

I want the query to get the values inside the [] as a parameter. so i'll give it a string like this: "1,2,3,4" and then it'll just query the table as following:
"SELECT * FROM tbl_users WHERE user_id IN (1,2,3,4)"

How can I do so with access 2003 and queries?

Thanks a lot,
Sagi

[Cstick]

I'm not sure if it is possible in Access, but in SQL server I would pass the comma delimited string to a custom function which would split the values and return a table with a record for each value, so "1,2,3,4" would return 4 records. If custom functions are available in Access, then do a search for "SQL Split Function" and you'll surely get some useful hits.

DECLARE @Values nvarchar(500);
SET @Values = '1,2,3,4';

SELECT * FROM tbl_users WHERE user_id IN (SELECT * FROM dbo.cfn_Split(@Values));

[eralper]

Hi,

You can use this split function for sql server


CREATE FUNCTION dbo.SPLIT (@strVal nvarchar(1000))
RETURNS
@StringTable table (
StringValue nvarchar(1000)
)
AS
BEGIN

declare @separator char(1)
set @separator = ','

declare @separator_position int
declare @array_value varchar(1000)

set @strVal = @strVal + ','

while patindex('%,%' , @strVal) <> 0
begin

select @separator_position = patindex('%,%' , @strVal)
select @array_value = left(@strVal, @separator_position - 1)

Insert @StringTable
Values (@array_value)

select @strVal = stuff(@strVal, 1, @separator_position, '')
end

RETURN
END

Eralper
http://www.kodyaz.com

[sagirodin]

Hi, maybe I can write the function by myself though i can't understand what the function should return? cause if I give this string in the parameter on my page:
"1, 3, 21, 5" , then what the function should return if it gets this parameter?

I mean this will be the querystring right?
SELECT * FROM tbl_users WHERE user_id IN (SELECT * FROM dbo.cfn_Split("1, 3, 21, 5"));

So howcom it'll work? what will the dbo.cfn_Split("1, 3, 21, 5" return to the query?

Thanks again very much,
Sagi.

[eralper]

Hi,

You can create user-defined functions that returns table data.

CREATE FUNCTION dbo.SPLIT (@strVal nvarchar(1000))
RETURNS
@StringTable table (
StringValue nvarchar(1000)
)
AS
...

This function returns a table containing a column named "StringValue"
This type of functions are different than scalar functions because table data type functions returns a row set of data with a tabular structure defined within the function definition.

Eralper
http://www.kodyaz.com

[sagirodin]

Ok I see. This sounds very clever. Is there someone who can help me doing this in VBScript in the module inside my access project? cause i really don't know how can create a function which will return a data table in VBScript.

Thanks a lot in advance and thanks for all the replies,
Sagi Rodin.