[AccessD] Query based on multiselect listbox

What you could do is use code like the one below to step through your 
selected items and put them into the in clause.  (I just took parts of it 
out of one of my apps).

Dim makesql As String
Dim wheresql As String
Dim sWhere As String
Dim rsTmp As DAO.Recordset
Dim dbTmp As DAO.Database
Dim myFieldName As String
Dim ctlDest As Control
Dim intCurrentRow As Integer
Dim myselectedname As String
Dim mynames As String

Set ctlDest = Forms![frmReportsAgingTR]![cmbFinalSort]
 
    For intCurrentRow = 0 To ctlDest.ListCount - 1
        'First set the variables
        myselectedname = ctlDest.Column(0, intCurrentRow)
        mynames = mynames & "'" & myselectedname & "', "
    Next intCurrentRow
 
    'Strip out last comma in list
    mynames = Left(mynames, (Len(mynames) - 2))

    If Me.cmbSelect = "Office" Then
        wheresql = " WHERE (((qryAgingTRDetail.Office) In (" & mynames & 
"))"
    Else
        wheresql = " WHERE (((qryAgingTRDetail.TR) In (" & mynames & "))"
    End If
 
    wheresql = wheresql & " AND ((qryAgingTRDetail.[TR Initial 
Review])='Past Due' Or (qryAgingTRDetail.[TR Initial Review])='Open'))"
    wheresql = wheresql & " ORDER BY qryAgingTRDetail.Office, 
qryAgingTRDetail.[Product Type], qryAgingTRDetail.[Deal Status]"




"Huffman, Jarad B." <jbhuffman at mdh.org>
Sent by: accessd-bounces at databaseadvisors.com
06/05/2003 08:08 AM
Please respond to accessd

 
        To:     "'accessd at databaseadvisors.com'" <accessd at databaseadvisors.com>
        cc: 
        Subject:        RE: [AccessD] Query based on multiselect listbox


I actually use a temp table to store the selected values and the textbox 
to
display the selected values.  Then I use the temp table in the query.  If
the record isn't in the temp table, the related records aren't selected. I
believe I got this from the Developer's Handbook (both 97 and 2002).

HTH,
Jarad Huffman

-----Original Message-----
From: Tim Thiessen [mailto:tim at irwin-greenhouses.com] 
Sent: Wednesday, June 04, 2003 4:21 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Query based on multiselect listbox


Dear Group

I am trying to use a multselect listbox to serve as a the parameter for a
query.  I'm using the IN operator but it doesn't seem to be working.  I'm
using A97.  Here is the SQL

SELECT Invoice.RDC
FROM Invoice
WHERE (((Invoice.RDC) In ([Forms]![frmWrkShts]![RDCCriteria]))
AND ((Invoice.[Invoice Date])<=[Forms]![frmWrkShts]![date]));

In the frmWrkShts, I have a text box (RDCCriteria) that is storing the 
items
selected from the listbox.  That part is working fine.  However, when I 
run
the query, 0 records are returned.  If I run the query with the items from
the text box, I get the correct number of records.  Is it not possible to
use the IN operator with a parameter query?

Tim Thiessen

_______________________________________________
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



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030605/f6c0c1b4/attachment-0001.html>