Multi-select list boxes in a parameter query

  • Thread starter Thread starter JimP
  • Start date Start date
J

JimP

I have read several ways to do this,

1. Create a comma delimited string of Itemselected values, presumably to use
in an "In" function in a SQL statement.
2. Same as above but using an " OR =" to append values in a string
3. Looping through the Itemselected collection and appending each value to a
table

Maybe more - is there a preferred way,or a way that seems to offer
performance advantages?

Is there a maximum length to the SQL string, as I can envision exceeding 255
characters in 1-2 above?
 
Question,

Do you loop through items in the ItemsSelected collection appending each
item to the table,

or create a QueryDef (from a string of the items) and then execute the
QueryDef?
 
Alex,

I posted this in an EXCEL forum a few weeks back, based on your reply in
this post, I believe you may be JUST the person to assist me this problem.
Below is my recent post:
=============
I wasn't really sure how to phrase this one -- this isn't your Typical
Drop-Down List.

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that the data queried shows products in 4 different cities,
without knowing (in advance) how could I create an input/drop-down field to
allow the user to specify a particular city?

Below is the code I'm using --
===================
Option Explicit
Sub DataExtractSpecific()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the DWS_Sales database on the Products Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" & _
"User Id=xxxxxxx;" & _
"Password=xxxxxx"


'Now open the connection.
cnExcel.Open strConn

On Error Resume Next

' Create a recordset object.
Dim OppNumber As String
Dim sqlCommand As String
'Dim CloseDate As Date
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
query.")
'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = " +
ProdNumber
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A3").CopyFromRecordset rsExcel

' Tidy up
.Close
End With

cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing

End Sub
===============
Many Thanks (in advance) for any assistance on this.

Shane
 
Hi,
not sure I understand the question. so you got data from SQL server into
excel - right? now you want to build a validation list in excel based on
that data?


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


doctorjones_md said:
Alex,

I posted this in an EXCEL forum a few weeks back, based on your reply in
this post, I believe you may be JUST the person to assist me this problem.
Below is my recent post:
=============
I wasn't really sure how to phrase this one -- this isn't your Typical
Drop-Down List.

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that the data queried shows products in 4 different cities,
without knowing (in advance) how could I create an input/drop-down field
to
allow the user to specify a particular city?

Below is the code I'm using --
===================
Option Explicit
Sub DataExtractSpecific()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the DWS_Sales database on the Products Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" & _
"User Id=xxxxxxx;" & _
"Password=xxxxxx"


'Now open the connection.
cnExcel.Open strConn

On Error Resume Next

' Create a recordset object.
Dim OppNumber As String
Dim sqlCommand As String
'Dim CloseDate As Date
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
query.")
'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = " +
ProdNumber
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A3").CopyFromRecordset rsExcel

' Tidy up
.Close
End With

cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing

End Sub
===============
Many Thanks (in advance) for any assistance on this.

Shane


Alex Dybenko said:
Hi,
for long multiselect list I also use temp table - I add all selected
items there and then join it to the table

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
Click to expand...
Click to expand...
 
Not quite -- what I mean is ...

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that a database lists products that have been delivered to 4
different cities, on 3 seperate days -- without knowing (in advance) the
delivery dates, how could I create an input/drop-down field to allow the
user to specify a particular date? I envision something like this:

VBA code produces a list box of delivery dates based on a particular product
and city. The user selects a delivery date from the list-boxe, and this
selection gets passed to a SQL Select Statement to return the data to an
EXCEL spreadsheet. I just don't know of any way to do this.

It appears to me (but I'm open to alternative suggestions) that I'll need to
perform an initial SQL query to create the list box, then pass the selection
from this list box to a subsequent SELECT statement for the final query.
I'm using a Date/Time stamp for the delivery date, so there isn't a way for
the user to know the exact delivery Date/Time without selecting it from a
list box.

I'm not certain that what I'm trying to do here is even possible -- any
thoughts on this?

Thanks for your prompt reply.

Shane


My data connection works fine, but I'm using a Date/Time stamp for
Alex Dybenko said:
Hi,
not sure I understand the question. so you got data from SQL server into
excel - right? now you want to build a validation list in excel based on
that data?


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


doctorjones_md said:
Alex,

I posted this in an EXCEL forum a few weeks back, based on your reply in
this post, I believe you may be JUST the person to assist me this
problem. Below is my recent post:
=============
I wasn't really sure how to phrase this one -- this isn't your Typical
Drop-Down List.

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that the data queried shows products in 4 different cities,
without knowing (in advance) how could I create an input/drop-down field
to
allow the user to specify a particular city?

Below is the code I'm using --
===================
Option Explicit
Sub DataExtractSpecific()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the DWS_Sales database on the Products Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" &
_
"User Id=xxxxxxx;" & _
"Password=xxxxxx"


'Now open the connection.
cnExcel.Open strConn

On Error Resume Next

' Create a recordset object.
Dim OppNumber As String
Dim sqlCommand As String
'Dim CloseDate As Date
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
query.")
'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = "
+
ProdNumber
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A3").CopyFromRecordset rsExcel

' Tidy up
.Close
End With

cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing

End Sub
===============
Many Thanks (in advance) for any assistance on this.

Shane


Alex Dybenko said:
Hi,
for long multiselect list I also use temp table - I add all selected
items there and then join it to the table

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I have read several ways to do this,

1. Create a comma delimited string of Itemselected values, presumably
to use in an "In" function in a SQL statement.
2. Same as above but using an " OR =" to append values in a string
3. Looping through the Itemselected collection and appending each value
to a table

Maybe more - is there a preferred way,or a way that seems to offer
performance advantages?

Is there a maximum length to the SQL string, as I can envision
exceeding 255 characters in 1-2 above?
Click to expand...
Click to expand...
Click to expand...
 
Hi,
I think you can do so using User Form in Excel, with listbox, you can show
it as a dialog.
actually you can put all your input controls on this user form, so user
first enter city, then select dates and then code fill the data

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

doctorjones_md said:
Not quite -- what I mean is ...

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that a database lists products that have been delivered to 4
different cities, on 3 seperate days -- without knowing (in advance) the
delivery dates, how could I create an input/drop-down field to allow the
user to specify a particular date? I envision something like this:

VBA code produces a list box of delivery dates based on a particular
product
and city. The user selects a delivery date from the list-boxe, and this
selection gets passed to a SQL Select Statement to return the data to an
EXCEL spreadsheet. I just don't know of any way to do this.

It appears to me (but I'm open to alternative suggestions) that I'll need
to
perform an initial SQL query to create the list box, then pass the
selection
from this list box to a subsequent SELECT statement for the final query.
I'm using a Date/Time stamp for the delivery date, so there isn't a way
for
the user to know the exact delivery Date/Time without selecting it from a
list box.

I'm not certain that what I'm trying to do here is even possible -- any
thoughts on this?

Thanks for your prompt reply.

Shane


My data connection works fine, but I'm using a Date/Time stamp for
Alex Dybenko said:
Hi,
not sure I understand the question. so you got data from SQL server into
excel - right? now you want to build a validation list in excel based on
that data?


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


doctorjones_md said:
Alex,

I posted this in an EXCEL forum a few weeks back, based on your reply
in this post, I believe you may be JUST the person to assist me this
problem. Below is my recent post:
=============
I wasn't really sure how to phrase this one -- this isn't your Typical
Drop-Down List.

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that the data queried shows products in 4 different cities,
without knowing (in advance) how could I create an input/drop-down field
to
allow the user to specify a particular city?

Below is the code I'm using --
===================
Option Explicit
Sub DataExtractSpecific()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the DWS_Sales database on the Products Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" &
_
"User Id=xxxxxxx;" & _
"Password=xxxxxx"


'Now open the connection.
cnExcel.Open strConn

On Error Resume Next

' Create a recordset object.
Dim OppNumber As String
Dim sqlCommand As String
'Dim CloseDate As Date
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
query.")
'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = "
+
ProdNumber
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A3").CopyFromRecordset rsExcel

' Tidy up
.Close
End With

cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing

End Sub
===============
Many Thanks (in advance) for any assistance on this.

Shane


Hi,
for long multiselect list I also use temp table - I add all selected
items there and then join it to the table

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I have read several ways to do this,

1. Create a comma delimited string of Itemselected values, presumably
to use in an "In" function in a SQL statement.
2. Same as above but using an " OR =" to append values in a string
3. Looping through the Itemselected collection and appending each
value to a table

Maybe more - is there a preferred way,or a way that seems to offer
performance advantages?

Is there a maximum length to the SQL string, as I can envision
exceeding 255 characters in 1-2 above?
Click to expand...
Click to expand...
Click to expand...
 
U¿ytkownik "doctorjones_md said:
Not quite -- what I mean is ...

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that a database lists products that have been delivered to 4
different cities, on 3 seperate days -- without knowing (in advance) the
delivery dates, how could I create an input/drop-down field to allow the
user to specify a particular date? I envision something like this:

VBA code produces a list box of delivery dates based on a particular
product
and city. The user selects a delivery date from the list-boxe, and this
selection gets passed to a SQL Select Statement to return the data to an
EXCEL spreadsheet. I just don't know of any way to do this.

It appears to me (but I'm open to alternative suggestions) that I'll need
to
perform an initial SQL query to create the list box, then pass the
selection
from this list box to a subsequent SELECT statement for the final query.
I'm using a Date/Time stamp for the delivery date, so there isn't a way
for
the user to know the exact delivery Date/Time without selecting it from a
list box.

I'm not certain that what I'm trying to do here is even possible -- any
thoughts on this?

Thanks for your prompt reply.

Shane


My data connection works fine, but I'm using a Date/Time stamp for
Alex Dybenko said:
Hi,
not sure I understand the question. so you got data from SQL server into
excel - right? now you want to build a validation list in excel based on
that data?


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


doctorjones_md said:
Alex,

I posted this in an EXCEL forum a few weeks back, based on your reply
in this post, I believe you may be JUST the person to assist me this
problem. Below is my recent post:
=============
I wasn't really sure how to phrase this one -- this isn't your Typical
Drop-Down List.

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that the data queried shows products in 4 different cities,
without knowing (in advance) how could I create an input/drop-down field
to
allow the user to specify a particular city?

Below is the code I'm using --
===================
Option Explicit
Sub DataExtractSpecific()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the DWS_Sales database on the Products Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" &
_
"User Id=xxxxxxx;" & _
"Password=xxxxxx"


'Now open the connection.
cnExcel.Open strConn

On Error Resume Next

' Create a recordset object.
Dim OppNumber As String
Dim sqlCommand As String
'Dim CloseDate As Date
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
query.")
'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = "
+
ProdNumber
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A3").CopyFromRecordset rsExcel

' Tidy up
.Close
End With

cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing

End Sub
===============
Many Thanks (in advance) for any assistance on this.

Shane


Hi,
for long multiselect list I also use temp table - I add all selected
items there and then join it to the table

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I have read several ways to do this,

1. Create a comma delimited string of Itemselected values, presumably
to use in an "In" function in a SQL statement.
2. Same as above but using an " OR =" to append values in a string
3. Looping through the Itemselected collection and appending each
value to a table

Maybe more - is there a preferred way,or a way that seems to offer
performance advantages?

Is there a maximum length to the SQL string, as I can envision
exceeding 255 characters in 1-2 above?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top