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

The 2k limit applies to the Access Listbox control's rowsource property. It
has nothing to do with the back end.

> -----Original Message-----
> From:	Susan Zeller [SMTP:szeller at cce.umn.edu]
> Sent:	Thursday, April 17, 2003 4:58 PM
> To:	accessd at databaseadvisors.com
> Subject:	RE: [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
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com