I have a query like this in my access

PARAMETERS ID Long;
SELECT *
FROM GROUPS
WHERE (((GROUPS.id) In ([ID])));


after I run this query i dialog box appear and it wants me to enter ID
value. I want to enter more than one ID value for this query. I use , as a
delimeter character.

It asks
ID
I enter
1,2,3

it returns only one record which has first (1) ID number.

How can I achieve this query ?

I dont want to use more than one variable like this. Because I dont have an
exact variable number. I can enter 1 or 4 or 6 parameter at a time .

SELECT *
FROM GROUPS
WHERE (((GROUPS.id) In ([@ID1,@ID2,@ID3])));

Savas
See Dejan's example

IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO

CREATE FUNCTION dbo.TsqlSplit
(@List As varchar(8000))
RETURNS @Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @Item As varchar(8000), @Pos As int
WHILE DATALENGTH(@List)>0
BEGIN
SET @Pos=CHARINDEX(',',@List)
IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
IF @Item<>'' INSERT INTO @Items SELECT @Item
SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
END
RETURN
END
GO

/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1


declare @inList varchar(50)
set @inList='10428,10429'

select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@InList)) As t
ON od.orderid = t.Item
"Savas Ates" <in da club> wrote in message
news:%(E-Mail Removed)...
>I have a query like this in my access
>
> PARAMETERS ID Long;
> SELECT *
> FROM GROUPS
> WHERE (((GROUPS.id) In ([ID])));
>
>
> after I run this query i dialog box appear and it wants me to enter ID
> value. I want to enter more than one ID value for this query. I use , as a
> delimeter character.
>
> It asks
> ID
> I enter
> 1,2,3
>
> it returns only one record which has first (1) ID number.
>
> How can I achieve this query ?
>
> I dont want to use more than one variable like this. Because I dont have
> an exact variable number. I can enter 1 or 4 or 6 parameter at a time .
>
> SELECT *
> FROM GROUPS
> WHERE (((GROUPS.id) In ([@ID1,@ID2,@ID3])));
>
>
>
>
>

It works well in Sql Query Analyser but how can i implement it to my access
? I tried to execute it via asp it returned parameter error. I tried to save
it a query in my access db it returned error again? how can i solce it ?

/////////////////////////////////////////////////////////////////////////////////////////////////////////////
DECLARE @inList VARCHAR(1000)

SELECT @inList='1,2,3'

select * from [groups]
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@inList)) As t
ON groups.id = t.Item



IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@List As varchar(8000))
RETURNS @Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @Item As varchar(8000), @Pos As int
WHILE DATALENGTH(@List)>0
BEGIN
SET @Pos=CHARINDEX(',',@List)
IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
IF @Item<>'' INSERT INTO @Items SELECT @Item
SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
END
RETURN
END
GO

What is the error?

CREATE PROCEDURE array_method_1
@array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @nsql nvarchar(4000)
SET @nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @array + ')'

PRINT @nsql
EXEC sp_executesql @nsql

END
GO


EXEC array_method_1
@array = '''sysobjects'',''sysindexes'',''syscolumns'''




"Savas Ates" <in da club> wrote in message
news:(E-Mail Removed)...
> It works well in Sql Query Analyser but how can i implement it to my
> access ? I tried to execute it via asp it returned parameter error. I
> tried to save it a query in my access db it returned error again? how can
> i solce it ?
>
> /////////////////////////////////////////////////////////////////////////////////////////////////////////////
> DECLARE @inList VARCHAR(1000)
>
> SELECT @inList='1,2,3'
>
> select * from [groups]
> INNER JOIN
> (SELECT Item
> FROM dbo.TsqlSplit(@inList)) As t
> ON groups.id = t.Item
>
>
>
> IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
> DROP FUNCTION dbo.TsqlSplit
> GO
> CREATE FUNCTION dbo.TsqlSplit
> (@List As varchar(8000))
> RETURNS @Items table (Item varchar(8000) Not Null)
> AS
> BEGIN
> DECLARE @Item As varchar(8000), @Pos As int
> WHILE DATALENGTH(@List)>0
> BEGIN
> SET @Pos=CHARINDEX(',',@List)
> IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
> SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
> IF @Item<>'' INSERT INTO @Items SELECT @Item
> SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
> END
> RETURN
> END
> GO
>

You wrote TSQL statement which i can use it in my MS-SQL server.
I cant write it in MS ACcess and save it as an query

How can i do it ? I think it is not possible to write
SET NOCOUNT ON
PRINT @nsql
EXEC sp_executesql @nsql

command in An Ms ACCEss query ?
If Im wrong can u explain how to it ?


"Uri Dimant" <(E-Mail Removed)>, haber iletisinde þunlarý
yazdý:(E-Mail Removed)...
> What is the error?
>
> CREATE PROCEDURE array_method_1
> @array nvarchar(4000)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @nsql nvarchar(4000)
> SET @nsql = '
> SELECT *
> FROM sysobjects
> WHERE name IN ( ' + @array + ')'
>
> PRINT @nsql
> EXEC sp_executesql @nsql
>
> END
> GO
>
>
> EXEC array_method_1
> @array = '''sysobjects'',''sysindexes'',''syscolumns'''
>
>
>
>
> "Savas Ates" <in da club> wrote in message
> news:(E-Mail Removed)...
>> It works well in Sql Query Analyser but how can i implement it to my
>> access ? I tried to execute it via asp it returned parameter error. I
>> tried to save it a query in my access db it returned error again? how can
>> i solce it ?
>>
>> /////////////////////////////////////////////////////////////////////////////////////////////////////////////
>> DECLARE @inList VARCHAR(1000)
>>
>> SELECT @inList='1,2,3'
>>
>> select * from [groups]
>> INNER JOIN
>> (SELECT Item
>> FROM dbo.TsqlSplit(@inList)) As t
>> ON groups.id = t.Item
>>
>>
>>
>> IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
>> DROP FUNCTION dbo.TsqlSplit
>> GO
>> CREATE FUNCTION dbo.TsqlSplit
>> (@List As varchar(8000))
>> RETURNS @Items table (Item varchar(8000) Not Null)
>> AS
>> BEGIN
>> DECLARE @Item As varchar(8000), @Pos As int
>> WHILE DATALENGTH(@List)>0
>> BEGIN
>> SET @Pos=CHARINDEX(',',@List)
>> IF @Pos=0 SET @Pos=DATALENGTH(@List)+1
>> SET @Item = LTRIM(RTRIM(LEFT(@List,@Pos-1)))
>> IF @Item<>'' INSERT INTO @Items SELECT @Item
>> SET @List=SUBSTRING(@List,@Pos+DATALENGTH(','),8000)
>> END
>> RETURN
>> END
>> GO
>>
>
>

Savas Ates wrote:
> You wrote TSQL statement which i can use it in my MS-SQL server.
> I cant write it in MS ACcess and save it as an query
>
> How can i do it ? I think it is not possible to write
> SET NOCOUNT ON
> PRINT @nsql
> EXEC sp_executesql @nsql
>
> command in An Ms ACCEss query ?
> If Im wrong can u explain how to it ?
>

Why did you crosspost this to a SQL Server newsgroup if you did not want
T-SQL answers?
Here is my canned answer I used to use for this type of question when I was
frequenting an Access mailing list:

There are two solutions for this problem listed in the following KB article
(Q210530 - ACC2000: How to Create a Parameter In() Statement), found by
searching for the keywords "parameter list query" (no quotes) at
http://support.microsoft.com.

http://support.microsoft.com/support.../Q210/5/30.ASP

The first solution uses Instr() to test the field values against the list in
the parameter. The second involves dynamically creating a SQL statement in
code.

Thanks to Paul Overway, here is a third solution, using the Eval function:

WHERE (((Eval([Table]![Field] & " In(" & [Forms]![Formname]![textboxname] &
")"))=True))

or, using a prompted parameter:

WHERE (((Eval([Table]![Field] & " In(" & [Enter List] & ")"))=True))


Thanks to Jeffrey A. Williams, here's a 4th solution:

If you don't mind adding a table to your database, and you're comfortable
dealing with possible multi-user issues, this will perform better than
either of the solutions that involve running a function (Instr or Eval) on
every row of your table:

Create a new table with two fields:

tblCriteria:
Criteria text
Selected boolean (yes/no)

Populate the table with your values and select a couple of items. Now you
can use this table in your query as such:

Select * from table1
inner join tblcriteria
on table1.[your criteria field] = tblcriteria.criteria
where tblcriteria.selected = true

You can easily setup a form (or subform) that is bound to tblCriteria and
allow the users the
ability of selecting which values they want.



Thanks to Michael Walsh, here's yet another way:

SELECT Table3.ConName, Table3.State, Table3.Zip
FROM Table3
WHERE "," &[list] & "," LIKE "*," & [ConName] & ",*"

with [param] some string like: '1,4,5,7'

note that there is no space after the comas.


It works simply. If AccountID is 45, clearly ',1,4,5,7,' LIKE
'*,45,*' returns false.
If AccountID is 4, on the other hand, ',1,4,5,7,' LIKE '*,4,*'
returns true.

So, you have, in effect, an IN( ) where the list is a parameter.




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"