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

Henry Simpson hsimpson88 at hotmail.com
Thu Apr 17 19:22:11 CDT 2003


If you want all the items in the list box and the rowsource is something 
like "Select FullName FROM tblContact Order By FullName;" and you wanted all 
the items in the list box in an IN clause, then in Access you can use the 
rowsource as a subquery inside the IN clause like:

WHERE ... IN (Select FullName FROM tblContact Order By FullName;)

I suppose when you are passing as a parameter to a stored procedure an SQL 
string, a rowsource from a local database won't work and you need a 
delimited list of values.

I'm do not know the rules of stored procedures but the 2k limit applies to 
form recordsources as well as list and combo sources in Access 97.  You may 
create a longer recordset SQL string in code and open it successfully or you 
can use a longer SQL statement to set a querydef SQL property.  I don't 
understand the comment about having nothing to do with the back end.

Hen


>From: "Heenan, Lambert" <Lambert.Heenan at AIG.com>
>Reply-To: accessd at databaseadvisors.com
>To: "'accessd at databaseadvisors.com'" <accessd at databaseadvisors.com>
>Subject: RE: [AccessD] Select all items in listbox and build sql string
>Date: Thu, 17 Apr 2003 17:15:12 -0400
>
>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

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail



More information about the AccessD mailing list