ACCESS PARAMETERS USING IN COMMAND

[Savas Ates]

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])));

 

[Uri Dimant]

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]

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

 

[Uri Dimant]

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]

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 ?

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

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

 

[Bob Barrows [MVP]]

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/kb/articles/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(

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

or, using a prompted parameter:

WHERE (((Eval(

![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 "," &

• & "," 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.