<br><font size=2 face="sans-serif">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).</font>
<br>
<br><font size=2 face="sans-serif">Dim makesql As String</font>
<br><font size=2 face="sans-serif">Dim wheresql As String</font>
<br><font size=2 face="sans-serif">Dim sWhere As String</font>
<br><font size=2 face="sans-serif">Dim rsTmp As DAO.Recordset</font>
<br><font size=2 face="sans-serif">Dim dbTmp As DAO.Database</font>
<br><font size=2 face="sans-serif">Dim myFieldName As String</font>
<br><font size=2 face="sans-serif">Dim ctlDest As Control</font>
<br><font size=2 face="sans-serif">Dim intCurrentRow As Integer</font>
<br><font size=2 face="sans-serif">Dim myselectedname As String</font>
<br><font size=2 face="sans-serif">Dim mynames As String</font>
<br>
<br><font size=2 face="sans-serif">Set ctlDest = Forms![frmReportsAgingTR]![cmbFinalSort]</font>
<br><font size=2 face="sans-serif"> </font>
<br><font size=2 face="sans-serif"> For intCurrentRow = 0 To ctlDest.ListCount - 1</font>
<br><font size=2 face="sans-serif"> 'First set the variables</font>
<br><font size=2 face="sans-serif"> myselectedname = ctlDest.Column(0, intCurrentRow)</font>
<br><font size=2 face="sans-serif"> mynames = mynames & "'" & myselectedname & "', "</font>
<br><font size=2 face="sans-serif"> Next intCurrentRow</font>
<br><font size=2 face="sans-serif"> </font>
<br><font size=2 face="sans-serif"> 'Strip out last comma in list</font>
<br><font size=2 face="sans-serif"> mynames = Left(mynames, (Len(mynames) - 2))</font>
<br>
<br><font size=2 face="sans-serif"> If Me.cmbSelect = "Office" Then</font>
<br><font size=2 face="sans-serif"> wheresql = " WHERE (((qryAgingTRDetail.Office) In (" & mynames & "))"</font>
<br><font size=2 face="sans-serif"> Else</font>
<br><font size=2 face="sans-serif"> wheresql = " WHERE (((qryAgingTRDetail.TR) In (" & mynames & "))"</font>
<br><font size=2 face="sans-serif"> End If</font>
<br><font size=2 face="sans-serif"> </font>
<br><font size=2 face="sans-serif"> wheresql = wheresql & " AND ((qryAgingTRDetail.[TR Initial Review])='Past Due' Or (qryAgingTRDetail.[TR Initial Review])='Open'))"</font>
<br><font size=2 face="sans-serif"> wheresql = wheresql & " ORDER BY qryAgingTRDetail.Office, qryAgingTRDetail.[Product Type], qryAgingTRDetail.[Deal Status]"</font>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td>
<td><font size=1 face="sans-serif"><b>"Huffman, Jarad B." <jbhuffman@mdh.org></b></font>
<br><font size=1 face="sans-serif">Sent by: accessd-bounces@databaseadvisors.com</font>
<p><font size=1 face="sans-serif">06/05/2003 08:08 AM</font>
<br><font size=1 face="sans-serif">Please respond to accessd</font>
<br>
<td><font size=1 face="Arial"> </font>
<br><font size=1 face="sans-serif"> To: "'accessd@databaseadvisors.com'" <accessd@databaseadvisors.com></font>
<br><font size=1 face="sans-serif"> cc: </font>
<br><font size=1 face="sans-serif"> Subject: RE: [AccessD] Query based on multiselect listbox</font></table>
<br>
<br>
<br><font size=2 face="Courier New">I actually use a temp table to store the selected values and the textbox to<br>
display the selected values. Then I use the temp table in the query. If<br>
the record isn't in the temp table, the related records aren't selected. I<br>
believe I got this from the Developer's Handbook (both 97 and 2002).<br>
<br>
HTH,<br>
Jarad Huffman<br>
<br>
-----Original Message-----<br>
From: Tim Thiessen [mailto:tim@irwin-greenhouses.com] <br>
Sent: Wednesday, June 04, 2003 4:21 PM<br>
To: accessd@databaseadvisors.com<br>
Subject: [AccessD] Query based on multiselect listbox<br>
<br>
<br>
Dear Group<br>
<br>
I am trying to use a multselect listbox to serve as a the parameter for a<br>
query. I'm using the IN operator but it doesn't seem to be working. I'm<br>
using A97. Here is the SQL<br>
<br>
SELECT Invoice.RDC<br>
FROM Invoice<br>
WHERE (((Invoice.RDC) In ([Forms]![frmWrkShts]![RDCCriteria]))<br>
AND ((Invoice.[Invoice Date])<=[Forms]![frmWrkShts]![date]));<br>
<br>
In the frmWrkShts, I have a text box (RDCCriteria) that is storing the items<br>
selected from the listbox. That part is working fine. However, when I run<br>
the query, 0 records are returned. If I run the query with the items from<br>
the text box, I get the correct number of records. Is it not possible to<br>
use the IN operator with a parameter query?<br>
<br>
Tim Thiessen<br>
<br>
_______________________________________________<br>
AccessD mailing list<br>
AccessD@databaseadvisors.com<br>
http://databaseadvisors.com/mailman/listinfo/accessd<br>
Website: http://www.databaseadvisors.com<br>
_______________________________________________<br>
AccessD mailing list<br>
AccessD@databaseadvisors.com<br>
http://databaseadvisors.com/mailman/listinfo/accessd<br>
Website: http://www.databaseadvisors.com<br>
<br>
</font>
<br>
<br>