build param string for WHERE IN (....)

[[email protected]]

Hi,
i'm trying to build a parameter in VBA as a list stings to pass it to
MSAccess procedure such as
"select ...from ...WHERE Strategy in ([@Strategy_List]);"

but I'm having problem with quotation marks around each string in a
list.. if only one name is in the list then query works just find but
additing second string to the list breaks it...


Set ParamStrategy = .CreateParameter("@Strategy_List", adVariant,
adParamInput)
'this one will work as only one string in the list
ParamStrategy.Value = "ALL_STRT"
param with two strings fails
'ParamStrategy.Value = "" & """ALL_G""" & "," & """ALL_O""" & ""

would appreciate any help on it
cheers
D.

[Tom Ogilvy]

See this:

http://www.dbforums.com/showthread.p...65#post4447965

--
Regards,
Tom Ogilvy



<[email protected]> wrote in message
news:[email protected]...
> Hi,
> i'm trying to build a parameter in VBA as a list stings to pass it to
> MSAccess procedure such as
> "select ...from ...WHERE Strategy in ([@Strategy_List]);"
>
> but I'm having problem with quotation marks around each string in a
> list.. if only one name is in the list then query works just find but
> additing second string to the list breaks it...
>
>
> Set ParamStrategy = .CreateParameter("@Strategy_List", adVariant,
> adParamInput)
> 'this one will work as only one string in the list
> ParamStrategy.Value = "ALL_STRT"
> param with two strings fails
> 'ParamStrategy.Value = "" & """ALL_G""" & "," & """ALL_O""" & ""
>
> would appreciate any help on it
> cheers
> D.
>

[[email protected]]

Tom, thank U for the response but the problem mentioned in the posting
(through the link U provided) is different from mine: i'm sending one
parameter which contains a list of strings (not a list of parameters)
but in the example from your link is an attempt to append parameter to
another parameter ...
well, at least as I'm interpreting my issue..
cheers
D.

[Tom Ogilvy]

Sounds to me like you either need a single string that has a list of comma
separated words in it

"red,blue,green"

or you need an array (if the parameter will accept an array).

--
Regards
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> Tom, thank U for the response but the problem mentioned in the posting
> (through the link U provided) is different from mine: i'm sending one
> parameter which contains a list of strings (not a list of parameters)
> but in the example from your link is an attempt to append parameter to
> another parameter ...
> well, at least as I'm interpreting my issue..
> cheers
> D.
>