How do I pass multiple criteria into a single parameter in vba?

I'd like to be able to pass multiple criteria into a single parameter in a vba code which calls a sql procedure.

In creating the command to call the procedure I've used is:
      cmd1.Parameters("@clientcode") = "AB3,BM1"
which returns no result.

The section of the sql code which relates to the parameter is:
      where T01.ClientCode in (@clientcode)

Any thoughts?  
ajnortonAsked:
Who is Participating?
 
WiBConnect With a Mentor Commented:
you may not assign parameter @clientcode to your cmd1, but may just format  the CommandText,
inserting "AB3,BM1" or whatever you need into the string (formatting the string) that contains the command text
0
 
cyberkiwiCommented:
Create the function below, then change your code from

      where T01.ClientCode in (@clientcode)

to

      where T01.ClientCode in (select value from values2table(@clientcode, ','))
CREATE function [dbo].[values2table]
(
@values varchar(max),
@separator varchar(2)
) returns @res table ([value] varchar(100))
as
begin
declare @value int
declare @commapos int, @lastpos int
set @commapos = 0
select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
while @commapos > @lastpos
begin
	select @value = substring(@values, @lastpos+1, @commapos-@lastpos-1)
	insert into @res select @value where @value <> ''
	select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
end
select @value = substring(@values, @lastpos+1, len(@values))
insert into @res select @value where @value <> ''
return
end
GO

Open in new window

0
 
ajnortonAuthor Commented:
Thanks for the reply.
I've just got to duck out of office and will look at it in more detail tomorrow.
Thanks again!
0
 
ajnortonAuthor Commented:
Sorry its taken so long in getting back.

Thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.