ACCESS PARAMETERS USING IN COMMAND

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:%23eBsFBRPGHA.1124@.TK2MSFTNGP10.phx.gbl...
>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'',''syscolu
mns'''
"Savas Ates" <in da club> wrote in message
news:e0lRJiRPGHA.3360@.TK2MSFTNGP09.phx.gbl...
> 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" <urid@.iscar.co.il>, haber iletisinde unlar
yazd:ud9i0rRPGHA.344@.TK2MSFTNGP11.phx.gbl...
> 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'',''syscolu
mns'''
>
>
> "Savas Ates" <in da club> wrote in message
> news:e0lRJiRPGHA.3360@.TK2MSFTNGP09.phx.gbl...
>|||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/suppor...s/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"