473,320 Members | 2,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

dynamic param for WHERE ...IN ( @param )

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

(@Strategy_List would be a bunch of names such as : "ALL", "ALL_G",
etc)

but I'm having problem with quotation marks around each string in a
list.. if only one string is in the list then query works just find
but
additing second string to the list does not return anything from a
query.

extract from my code:
Set adoCmd = New ADODB.Command
With adoCmd
Set .ActiveConnection = cn
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""" & ""
.Parameters.Append ParamStrategy
end with

Set rs = New ADODB.Recordset
Set rs = adoCmd.Execute

would appreciate any help on it
cheers
D.

Nov 13 '05 #1
2 1642
One alternative would be the following:

'ParamStrategy.Value = """" & "ALL_G" & """" & "," & """" & "ALL_O" & """"

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"dim" <di*****@yahoo.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...
Hi,
i'm trying to build a parameter in VBA as a list of stings to pass it
to
MSAccess procedure such as
"select ...from ...WHERE Strategy in ([@Strategy_List]);"

(@Strategy_List would be a bunch of names such as : "ALL", "ALL_G",
etc)

but I'm having problem with quotation marks around each string in a
list.. if only one string is in the list then query works just find
but
additing second string to the list does not return anything from a
query.

extract from my code:
Set adoCmd = New ADODB.Command
With adoCmd
Set .ActiveConnection = cn
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""" & ""
.Parameters.Append ParamStrategy
end with

Set rs = New ADODB.Recordset
Set rs = adoCmd.Execute

would appreciate any help on it
cheers
D.
Nov 13 '05 #2
On 6 Oct 2005 13:04:46 -0700, "dim" <di*****@yahoo.com> wrote:

I've tried to do this several times, and always failed. I think it's a
limitation of the query processor.
You could execute a dynamic SQL statement.

-Tom.

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

(@Strategy_List would be a bunch of names such as : "ALL", "ALL_G",
etc)

but I'm having problem with quotation marks around each string in a
list.. if only one string is in the list then query works just find
but
additing second string to the list does not return anything from a
query.

extract from my code:
Set adoCmd = New ADODB.Command
With adoCmd
Set .ActiveConnection = cn
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""" & ""
.Parameters.Append ParamStrategy
end with

Set rs = New ADODB.Recordset
Set rs = adoCmd.Execute

would appreciate any help on it
cheers
D.


Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jedediah Smith | last post by:
In order to facilitate search engine indexing, I'd like to be able to access a script using something like this: http://server/script.asp/param where this would execute script.asp which could...
4
by: Marc | last post by:
Hello, I'm not an expert in SQL, if you could help me for that little problem: I had tree simple tables with their fields: IdClient, Param IdSale, IdClient, Param IdParam, Value
2
by: systemutvecklare | last post by:
Hi! I have an application that generates an html-form from an xml-file using an xsl-file. My problem is that I want the xsl to use some "unknown" parameters that I pass to the xslt processor...
2
by: Rehor | last post by:
I'm just starting with ASP.NET and C#. On one of my web forms I created has a dropdown list of choices. Once a choice is made, the postbacked page displays a list of links to documents in an...
1
by: Rich | last post by:
Hello, I can update a dataset from my client app using a dataAdapter.Updatecommand when I add parameter values outside of the param declaration. But If I add the param values inline with the...
7
by: kforski | last post by:
Hello I wonder how can I find out if the param given to the xsl is defined without error during compilation. Actually I have such code (a part of it) <xsl:if test="$showWarnings='true']">...
1
by: lyealain | last post by:
select * from cbomnew where (comCode= 'PCEC'or comCode='L0X'or comCode='CCEC') expert please help me on this... the comCode after WHERE statement are static(hardcoded).. PCEC,L0X,CCEC ... ...
6
by: =?ISO-8859-1?Q?Tim_B=FCthe?= | last post by:
Hi, we are building a Java webapplication using JSF, running on websphere, querying a DB2 9 on Suse Enterprise 10. The app uses JDBC and PreparedStatements only (aka dynamic SQL). Every night,...
0
Curtis Rutland
by: Curtis Rutland | last post by:
This isn't a full-blown article, just a quickie. But I couldn't pass up the opportunity to share this insight with people, because it made my coding life sooooooo much easier. ...
0
by: yshali | last post by:
I am trying to handle the NULL value passed to a parameter. if param1 = 1,2,3 query returns where param in (1,2,3) if param = NULL query returns all values. I have a split function to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.