Paramters with Multiple Values

I know I must be missing something obvious but I cannot find it in the
documentation. How do I allow the user to input multiple values for a report
parameter. For example, the main query has this for test criteria:
where CustID in(100, 200, 300)
so I changed to:
IN(@.CustId) expecting to input 100,200,300
but instead I get an error "Application uses a value of the wrong type for
the current operation"
? ThanksTo do that you have to write dynamic sql and your parameter has to be a
varchar... the reason you are getting an error is you are comparing an
INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
"100,200,300" to integers.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
> I know I must be missing something obvious but I cannot find it in the
> documentation. How do I allow the user to input multiple values for a
report
> parameter. For example, the main query has this for test criteria:
> where CustID in(100, 200, 300)
> so I changed to:
> IN(@.CustId) expecting to input 100,200,300
> but instead I get an error "Application uses a value of the wrong type for
> the current operation"
> ? Thanks
>|||So does that mean if it were a varchar column it would accept the commas and
treat them as delimiters between separate values ie 100 OR 200 OR 300?
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:em72MOCyEHA.4044@.TK2MSFTNGP10.phx.gbl...
> To do that you have to write dynamic sql and your parameter has to be a
> varchar... the reason you are getting an error is you are comparing an
> INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
> "100,200,300" to integers.
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
>> I know I must be missing something obvious but I cannot find it in the
>> documentation. How do I allow the user to input multiple values for a
> report
>> parameter. For example, the main query has this for test criteria:
>> where CustID in(100, 200, 300)
>> so I changed to:
>> IN(@.CustId) expecting to input 100,200,300
>> but instead I get an error "Application uses a value of the wrong type
>> for
>> the current operation"
>> ? Thanks
>>
>|||No, it would treat it as one string... the easiest way to do a query with a
parameter that contains multiple integer values is dynamic sql like...
DECLARE @.CustID VARCHAR(100)
DECLARE @.sql VARCHAR(1000)
SET @.CustID = '100,200,300'
SELECT @.sql = 'SELECT * FROM Customer WHERE CustID IN (' + @.CustID + ')'
EXEC(@.SQL)
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:O1XluJDyEHA.2212@.TK2MSFTNGP15.phx.gbl...
> So does that mean if it were a varchar column it would accept the commas
and
> treat them as delimiters between separate values ie 100 OR 200 OR 300?
> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
> news:em72MOCyEHA.4044@.TK2MSFTNGP10.phx.gbl...
> > To do that you have to write dynamic sql and your parameter has to be a
> > varchar... the reason you are getting an error is you are comparing an
> > INTEGER (CustID) to a VARCHAR. Or you have to some how parse the string
> > "100,200,300" to integers.
> >
> >
> > "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> > news:eyRMUFCyEHA.1392@.tk2msftngp13.phx.gbl...
> >> I know I must be missing something obvious but I cannot find it in the
> >> documentation. How do I allow the user to input multiple values for a
> > report
> >> parameter. For example, the main query has this for test criteria:
> >> where CustID in(100, 200, 300)
> >> so I changed to:
> >> IN(@.CustId) expecting to input 100,200,300
> >> but instead I get an error "Application uses a value of the wrong type
> >> for
> >> the current operation"
> >>
> >> ? Thanks
> >>
> >>
> >
> >
>