[AccessD] Select all items in listbox and build sql string

Henry,

Great, that's what I needed.  

Yes, I'm trying to build a delimited list for use in an IN clause.  I'm
passing the in clause as an input parameter to a sproc in Sql Server
2000.  I'm in an adp.  I can't just drop the in clause b/c the list box
values are not all the values for the field in question.  There is
another way around this that I am considering.  I didn't know about the
2k limit but I don't think that is an issue with SQL server.

--Susan


-----Original Message-----
From: Henry Simpson [mailto:hsimpson88 at hotmail.com] 
Sent: Thursday, April 17, 2003 3:41 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Select all items in listbox and build sql string


Susan:

Why wouldn't the row source of the ListBox alone work?  Are you making a

delimited list for use in an IN(...) clause?  If so, you could just drop
the 
row source in the IN clause if it is an SQL string.

If you have too many items selected in the list, the string you build 
through concatenation could exceed the 2k character limit of a
dynamically 
generated SQL string.  At that point it would be necessary to use a
saved 
querydef and change its SQL property.

To build a string, you iterate from 0 to ctl.ListCount -1 instead of the

iterating ItemsSelected collection.

Your usage of ctl before .column is redundant as it is inside a With ctl
code block.

Hen


>From: "Susan Zeller" <szeller at cce.umn.edu>
>Reply-To: accessd at databaseadvisors.com
>To: <accessd at databaseadvisors.com>
>Subject: [AccessD] Select all items in listbox and build sql string
>Date: Thu, 17 Apr 2003 13:58:37 -0500
>
>I have some code from Arthur Fuller that makes a string out of all the 
>selected items in a listbox.  What I need now is to build a string out 
>of All the items in the listbox whether they are selected or not. I'm 
>not sure how to adapt the code I have.  I'm guessing I need to do For 0

>to the number of items in the listbox in some way, but not sure of the 
>syntax for this.  Here's what I have now:
>
>     Dim intI As Integer, intX As Integer
>     Dim strSQL As String
>
>         With ctl
>             For intI = 0 To .ItemsSelected.Count - 1
>                 intX = ctl.ItemsSelected(intI)
>                 If intI > 0 Then
>                     strSQL = strSQL & ",'" & ctl.Column(0, intX) & "'"
>                 Else
>                     strSQL = "'" & ctl.Column(0, intX) & "'"
>                 End If
>             Next intI
>         End With
>
>-Susan
>
>
>Susan B. Zeller
>Office of Information Systems
>College of Continuing Education
>University of Minnesota
>306 Wesbrook Hall
>77 Pleasant Street SE
>Minneapolis, MN 55455
>Phone:  612-626-4785
>Fax:  612-625-2568


_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.  
http://join.msn.com/?page=features/featuredemail

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com