?
Solved

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

Posted on 2010-09-08
4
Medium Priority
?
284 Views
Last Modified: 2012-05-10
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?  
0
Comment
Question by:ajnorton
  • 2
4 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33625902
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
 
LVL 3

Accepted Solution

by:
WiB earned 1000 total points
ID: 33626432
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
 

Author Comment

by:ajnorton
ID: 33626938
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
 

Author Comment

by:ajnorton
ID: 33767498
Sorry its taken so long in getting back.

Thanks for the help!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question