By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,663 Members | 2,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,663 IT Pros & Developers. It's quick & easy.

Code for Filtering from Mult-select List Box Criteria

P: n/a
Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to
make the second list box (lstAcadPlan) a multi-select list box and pass the
values to the filter. I have no idea how to include that in my code and was
wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_Click()
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan &
"'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub

Thank you,

William
Nov 12 '05 #1
Share this Question
Share on Google+
19 Replies


P: n/a
On my website (see sig below) is a small sample database called
"CreateQueries2.mdb". Form 6 in this database illustrates how to create a
Where condition in code with a multi-select listbox. See the code behind
the form for details. In broad outline, it will work something like this:

For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

This code loops through the values in the listbox. When it reaches one that
is selected, it adds to the Where clause. When it reaches the end, it lops
the last comma off of the Where clause.

(The actual syntax of the strWhere clause will vary depending on details of
your listbox control.)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to make the second list box (lstAcadPlan) a multi-select list box and pass the values to the filter. I have no idea how to include that in my code and was wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_Click()
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan &
"'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub

Thank you,

William

Nov 12 '05 #2

P: n/a
On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

Check the SelectedItems collection. Then build an IN clause as part of
your WHERE statement:
.... and AcadPlan in ('aaa', 'bbb', 'ccc')

-Tom.

Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to
make the second list box (lstAcadPlan) a multi-select list box and pass the
values to the filter. I have no idea how to include that in my code and was
wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_Click()
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan &
"'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub

Thank you,

William


Nov 12 '05 #3

P: n/a
Thank you for your reply Tom.

I'm not familiar with the IN clause. What do aaa, bbb, ccc represent? The
values of second list box will change depending on what is selected in the
first list box. For example, there can be up to 50 distinct Academic Plans
for each College chosen in the first list box. Also, what are the
"SelectedItems" collection?

William
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:7n********************************@4ax.com...
On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

Check the SelectedItems collection. Then build an IN clause as part of
your WHERE statement:
... and AcadPlan in ('aaa', 'bbb', 'ccc')

-Tom.

Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) basedon the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need tomake the second list box (lstAcadPlan) a multi-select list box and pass thevalues to the filter. I have no idea how to include that in my code and waswondering if anyone had any ideas on what I should do. Here is the code Ihave so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_Click()
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan &
"'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub

Thank you,

William

Nov 12 '05 #4

P: n/a
Thanks Roger,

I tried your code. I get a run time error and when I go to debug, it seems
to have combined the college and academic plan. For example, strWhere
filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy, Or
History". But it is showing [College]=CAS, Art, Philosophy, History."

William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:O7****************@tk2msftngp13.phx.gbl...
On my website (see sig below) is a small sample database called
"CreateQueries2.mdb". Form 6 in this database illustrates how to create a
Where condition in code with a multi-select listbox. See the code behind
the form for details. In broad outline, it will work something like this:

For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

This code loops through the values in the listbox. When it reaches one that is selected, it adds to the Where clause. When it reaches the end, it lops the last comma off of the Where clause.

(The actual syntax of the strWhere clause will vary depending on details of your listbox control.)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need
to
make the second list box (lstAcadPlan) a multi-select list box and pass

the
values to the filter. I have no idea how to include that in my code and

was
wondering if anyone had any ideas on what I should do. Here is the code

I have so far that works fine as long as only one value is selected in the
second list box:

Private Sub cmdShowResults_Click()
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan & "'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub

Thank you,

William


Nov 12 '05 #5

P: n/a
OK. Sorry, I missed a bit. Try this:

strWhere = "Where [College] = '" & Me.lstCollege & "' and "
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Thanks Roger,

I tried your code. I get a run time error and when I go to debug, it seems to have combined the college and academic plan. For example, strWhere
filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy, Or History". But it is showing [College]=CAS, Art, Philosophy, History."

William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:O7****************@tk2msftngp13.phx.gbl...
On my website (see sig below) is a small sample database called
"CreateQueries2.mdb". Form 6 in this database illustrates how to create a
Where condition in code with a multi-select listbox. See the code behind the form for details. In broad outline, it will work something like this:
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

This code loops through the values in the listbox. When it reaches one that
is selected, it adds to the Where clause. When it reaches the end, it

lops
the last comma off of the Where clause.

(The actual syntax of the strWhere clause will vary depending on details

of
your listbox control.)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Hello Everyone,

I have a main form with a datasheet subform that I use to query by form. After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records based on the two criteria. The criteria used on the main form are values selected in two list boxes. When the user clicks on the first list box (lstCollege), it returns values in the second list box (lstAcadPlan) based on the first. The user then clicks on the cmdShowResults to filter and return records in the datasheet subform. This works fine except for one problem. Both list boxes are set up for single select values--I now need
to
make the second list box (lstAcadPlan) a multi-select list box and pass the
values to the filter. I have no idea how to include that in my code
and was
wondering if anyone had any ideas on what I should do. Here is the

code I have so far that works fine as long as only one value is selected in
the second list box:

Private Sub cmdShowResults_Click()
Me.sfrmSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [AcadPlan] = '" &
Me.lstAcadPlan & "'"
Else
strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
End If
End If
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
End Sub

Thank you,

William



Nov 12 '05 #6

P: n/a
On Mon, 22 Dec 2003 14:11:52 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

IN clause: that's a learning opportunity.

aaa, bbb etc represent possible text values of the selected items.

SelectedItems: slip of the pen. I meant: selected items. The Selected
property of a multiselect listbox contains the selected items. See
help file for more details.

-Tom.

Thank you for your reply Tom.

I'm not familiar with the IN clause. What do aaa, bbb, ccc represent? The
values of second list box will change depending on what is selected in the
first list box. For example, there can be up to 50 distinct Academic Plans
for each College chosen in the first list box. Also, what are the
"SelectedItems" collection?

William
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:7n********************************@4ax.com.. .
On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

Check the SelectedItems collection. Then build an IN clause as part of
your WHERE statement:
... and AcadPlan in ('aaa', 'bbb', 'ccc')

-Tom.

>Hello Everyone,
>
>I have a main form with a datasheet subform that I use to query by form.
>After the user selects two criteria on the main form and clicks the
>cmdShowResults button on the main form, the subform returns the records
>based on the two criteria. The criteria used on the main form are values
>selected in two list boxes. When the user clicks on the first list box
>(lstCollege), it returns values in the second list box (lstAcadPlan)based >on the first. The user then clicks on the cmdShowResults to filter and
>return records in the datasheet subform. This works fine except for one
>problem. Both list boxes are set up for single select values--I now needto >make the second list box (lstAcadPlan) a multi-select list box and passthe >values to the filter. I have no idea how to include that in my code andwas >wondering if anyone had any ideas on what I should do. Here is the codeI >have so far that works fine as long as only one value is selected in the
>second list box:
>
>Private Sub cmdShowResults_Click()
>Me.sfrmSearchResults.Visible = True
>Me.lblSubformInstructions.Visible = True
> Dim strWhere As String
> Dim rst As Recordset
>
> If Len(Me.lstCollege & "") > 0 Then
> strWhere = "[College] = '" & Me.lstCollege & "'"
> End If
>
> If Len(Me.lstAcadPlan & "") > 0 Then
> If Len(strWhere) > 0 Then
> strWhere = strWhere & "And [AcadPlan] = '" & Me.lstAcadPlan &
>"'"
> Else
> strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
> End If
> End If
> Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
> Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
>End Sub
>
>Thank you,
>
>William
>


Nov 12 '05 #7

P: n/a
William Wisnieski wrote:
Hello Everyone,

I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box (lstAcadPlan) based
on the first. The user then clicks on the cmdShowResults to filter and
return records in the datasheet subform. This works fine except for one
problem. Both list boxes are set up for single select values--I now need to
make the second list box (lstAcadPlan) a multi-select list box and pass the
values to the filter. I have no idea how to include that in my code and was
wondering if anyone had any ideas on what I should do. Here is the code I
have so far that works fine as long as only one value is selected in the
second list box:


This is aircode and not tested but close enough for you to play with
If Me.lstCollege.ItemsSelected.Count = 0 or Me.lstPlans.Count = 0 then
msgbox "Please select the college and then the academic plans."
else
Dim strWhere as string
Dim strHold As String
Dim varID as Variant
strWhere = "[College] = '" & Me.lstCollege & "' And "
For each varID in Me.lstPlans.ItemsSelected 'loop through all items selected

strHold = strHold & Me.lstPlans.Itemdata(varID) & ", "
Next
strHold = :(" & Left(strHold,Len(strHold)-2) & ")" 'remove space and comma,
surround in parantheses
Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere & " And AcadPlan
In " & strHold
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
end if

Check out the IN predicate for SQL
Nov 12 '05 #8

P: n/a
Thanks Roger. I used your code and it works great. I'm trying to modify it
now for two reasons. Now, I have to add some more search criteria (State,
HonorsCollege, EMPLID, LastName) on the main form. Second, I need the user
to be able to select from the college list without being required to select
from the AcadPlan list.

Here's what I have that works so far:

Private Sub cmdShowResults_Click()
Me.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True

==================================

Now here is what I have added. The good news is it doesn't return any
errors. The bad news is it doesn't filter any records:

Private Sub cmdShowResults_Click()
Me.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If

If Len(Me.lstHonors & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [HonorsCollege] = '" & Me.lstHonors &
"'"
Else
strWhere = "[HonorsCollege] = '" & Me.lstHonors & "'"
End If

End If
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

If Len(Me.txtEMPLID & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If
End If

If Len(Me.txtLast & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
End If

Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True

Thanks again for your help. It is much appreciated.

William



"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:uc**************@TK2MSFTNGP09.phx.gbl...
OK. Sorry, I missed a bit. Try this:

strWhere = "Where [College] = '" & Me.lstCollege & "' and "
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Thanks Roger,

I tried your code. I get a run time error and when I go to debug, it seems
to have combined the college and academic plan. For example, strWhere
filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy,

Or
History". But it is showing [College]=CAS, Art, Philosophy, History."

William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:O7****************@tk2msftngp13.phx.gbl...
On my website (see sig below) is a small sample database called
"CreateQueries2.mdb". Form 6 in this database illustrates how to
create a Where condition in code with a multi-select listbox. See the code behind the form for details. In broad outline, it will work something like this:
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

This code loops through the values in the listbox. When it reaches
one
that
is selected, it adds to the Where clause. When it reaches the end, it

lops
the last comma off of the Where clause.

(The actual syntax of the strWhere clause will vary depending on
details of
your listbox control.)

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
> Hello Everyone,
>
> I have a main form with a datasheet subform that I use to query by

form. > After the user selects two criteria on the main form and clicks the
> cmdShowResults button on the main form, the subform returns the records > based on the two criteria. The criteria used on the main form are

values
> selected in two list boxes. When the user clicks on the first list box > (lstCollege), it returns values in the second list box (lstAcadPlan)

based
> on the first. The user then clicks on the cmdShowResults to filter and > return records in the datasheet subform. This works fine except for one > problem. Both list boxes are set up for single select values--I now

need
to
> make the second list box (lstAcadPlan) a multi-select list box and pass the
> values to the filter. I have no idea how to include that in my code and was
> wondering if anyone had any ideas on what I should do. Here is the code
I
> have so far that works fine as long as only one value is selected in

the > second list box:
>
> Private Sub cmdShowResults_Click()
> Me.sfrmSearchResults.Visible = True
> Me.lblSubformInstructions.Visible = True
> Dim strWhere As String
> Dim rst As Recordset
>
> If Len(Me.lstCollege & "") > 0 Then
> strWhere = "[College] = '" & Me.lstCollege & "'"
> End If
>
> If Len(Me.lstAcadPlan & "") > 0 Then
> If Len(strWhere) > 0 Then
> strWhere = strWhere & "And [AcadPlan] = '" &

Me.lstAcadPlan
&
> "'"
> Else
> strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
> End If
> End If
> Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
> Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> End Sub
>
> Thank you,
>
> William
>
>



Nov 12 '05 #9

P: n/a
When you say it does not filter, do you mean All records are returned or No
records are returned?

Well, I see some obvious problems, none of which may be the major cause.

1) This section:
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
should have an AND like so:
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND [AcadPlan] IN ("
2) This section: If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If is using two different datatypes. If EMPLID is text, the first should be
used, if it is numeric, the second should be used

3) This is not causing a problem yet, but may in the future. If you have a
person with a Last name of O'Brien, this code will fail: If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
To fix, replace each apostrophe (') with TWO quotes ("")
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = """ & Me.txtLast & """"
Else
strWhere = "[Last] = """ & Me.txtLast & """"
End If
Now, as I said, these may not be the cause of the problem at hand. The best
thing to do in a situation like this is to put the following line:

Debug.Print strWhere

just before the filtering line. Then put a break point on the first filter
line. The code will stop before the filter executes and if you look in the
Debug (Immediate) window, you will see exactly what your Where clause looks
like. You can even cut and paste it into a query and see what it is
acutally returning or what error messages it produces.

HTH

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu... Thanks Roger. I used your code and it works great. I'm trying to modify it now for two reasons. Now, I have to add some more search criteria (State,
HonorsCollege, EMPLID, LastName) on the main form. Second, I need the user to be able to select from the college list without being required to select from the AcadPlan list.

Here's what I have that works so far:

Private Sub cmdShowResults_Click()
Me.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True

==================================

Now here is what I have added. The good news is it doesn't return any
errors. The bad news is it doesn't filter any records:

Private Sub cmdShowResults_Click()
Me.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If

If Len(Me.lstHonors & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [HonorsCollege] = '" & Me.lstHonors & "'"
Else
strWhere = "[HonorsCollege] = '" & Me.lstHonors & "'"
End If

End If
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

If Len(Me.txtEMPLID & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If
End If

If Len(Me.txtLast & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
End If

Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True

Thanks again for your help. It is much appreciated.

William



"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:uc**************@TK2MSFTNGP09.phx.gbl...
OK. Sorry, I missed a bit. Try this:

strWhere = "Where [College] = '" & Me.lstCollege & "' and "
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Thanks Roger,

I tried your code. I get a run time error and when I go to debug, it

seems
to have combined the college and academic plan. For example, strWhere
filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy,
Or
History". But it is showing [College]=CAS, Art, Philosophy, History."

William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:O7****************@tk2msftngp13.phx.gbl...
> On my website (see sig below) is a small sample database called
> "CreateQueries2.mdb". Form 6 in this database illustrates how to create
a
> Where condition in code with a multi-select listbox. See the code

behind
> the form for details. In broad outline, it will work something like

this:
>
> For i = 0 To lstAcadPlan.ListCount - 1
> If lstAcadPlan.Selected(i) Then
> strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
> End If
> Next i
> strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
>
> This code loops through the values in the listbox. When it reaches one that
> is selected, it adds to the Where clause. When it reaches the end,
it lops
> the last comma off of the Where clause.
>
> (The actual syntax of the strWhere clause will vary depending on

details of
> your listbox control.)
>
> --
> --Roger Carlson
> www.rogersaccesslibrary.com
> Reply to: Roger dot Carlson at Spectrum-Health dot Org
>
>
> "William Wisnieski" <ww********@admissions.umass.edu> wrote in message > news:3f********@news-1.oit.umass.edu...
> > Hello Everyone,
> >
> > I have a main form with a datasheet subform that I use to query by

form.
> > After the user selects two criteria on the main form and clicks the > > cmdShowResults button on the main form, the subform returns the

records
> > based on the two criteria. The criteria used on the main form are
values
> > selected in two list boxes. When the user clicks on the first list box
> > (lstCollege), it returns values in the second list box
(lstAcadPlan) based
> > on the first. The user then clicks on the cmdShowResults to filter and
> > return records in the datasheet subform. This works fine except
for one
> > problem. Both list boxes are set up for single select values--I
now need
> to
> > make the second list box (lstAcadPlan) a multi-select list box and

pass
> the
> > values to the filter. I have no idea how to include that in my

code and
> was
> > wondering if anyone had any ideas on what I should do. Here is
the code
I
> > have so far that works fine as long as only one value is selected
in the
> > second list box:
> >
> > Private Sub cmdShowResults_Click()
> > Me.sfrmSearchResults.Visible = True
> > Me.lblSubformInstructions.Visible = True
> > Dim strWhere As String
> > Dim rst As Recordset
> >
> > If Len(Me.lstCollege & "") > 0 Then
> > strWhere = "[College] = '" & Me.lstCollege & "'"
> > End If
> >
> > If Len(Me.lstAcadPlan & "") > 0 Then
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [AcadPlan] = '" &

Me.lstAcadPlan
&
> > "'"
> > Else
> > strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
> > End If
> > End If
> > Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
> > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> > End Sub
> >
> > Thank you,
> >
> > William
> >
> >
>
>



Nov 12 '05 #10

P: n/a
Roger....thanks again.

Sorry for the confusion. Regarding the filtering problem, all records are
being returned. Also, I don't quite understand the If Len(strWhere). I'm
not sure which sections you are referring to. Should I be using that
instead of say, If Len(Me.lstCollege & "") > 0 Then.....................

William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
When you say it does not filter, do you mean All records are returned or No records are returned?

Well, I see some obvious problems, none of which may be the major cause.

1) This section:
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
should have an AND like so:
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND [AcadPlan] IN ("


2) This section:
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If

is using two different datatypes. If EMPLID is text, the first should be
used, if it is numeric, the second should be used

3) This is not causing a problem yet, but may in the future. If you have

a person with a Last name of O'Brien, this code will fail:
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
To fix, replace each apostrophe (') with TWO quotes ("")
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = """ & Me.txtLast & """"
Else
strWhere = "[Last] = """ & Me.txtLast & """"
End If


Now, as I said, these may not be the cause of the problem at hand. The

best thing to do in a situation like this is to put the following line:

Debug.Print strWhere

just before the filtering line. Then put a break point on the first filter line. The code will stop before the filter executes and if you look in the Debug (Immediate) window, you will see exactly what your Where clause looks like. You can even cut and paste it into a query and see what it is
acutally returning or what error messages it produces.

HTH

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Thanks Roger. I used your code and it works great. I'm trying to modify
it
now for two reasons. Now, I have to add some more search criteria
(State, HonorsCollege, EMPLID, LastName) on the main form. Second, I need the

user
to be able to select from the college list without being required to

select
from the AcadPlan list.

Here's what I have that works so far:

Private Sub cmdShowResults_Click()
Me.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True

==================================

Now here is what I have added. The good news is it doesn't return any
errors. The bad news is it doesn't filter any records:

Private Sub cmdShowResults_Click()
Me.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If

If Len(Me.lstHonors & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [HonorsCollege] = '" & Me.lstHonors &
"'"
Else
strWhere = "[HonorsCollege] = '" & Me.lstHonors & "'"
End If

End If
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

If Len(Me.txtEMPLID & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID &
"'" Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If
End If

If Len(Me.txtLast & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
End If

Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True

Thanks again for your help. It is much appreciated.

William



"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:uc**************@TK2MSFTNGP09.phx.gbl...
OK. Sorry, I missed a bit. Try this:

strWhere = "Where [College] = '" & Me.lstCollege & "' and "
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
> Thanks Roger,
>
> I tried your code. I get a run time error and when I go to debug, it seems
> to have combined the college and academic plan. For example, strWhere > filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy, Or
> History". But it is showing [College]=CAS, Art, Philosophy, History." >
> William
>
>
> "Roger Carlson" <NO**************@hotmail.com> wrote in message
> news:O7****************@tk2msftngp13.phx.gbl...
> > On my website (see sig below) is a small sample database called
> > "CreateQueries2.mdb". Form 6 in this database illustrates how to

create
a
> > Where condition in code with a multi-select listbox. See the code
behind
> > the form for details. In broad outline, it will work something like this:
> >
> > For i = 0 To lstAcadPlan.ListCount - 1
> > If lstAcadPlan.Selected(i) Then
> > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', " > > End If
> > Next i
> > strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
> >
> > This code loops through the values in the listbox. When it reaches one
> that
> > is selected, it adds to the Where clause. When it reaches the
end, it > lops
> > the last comma off of the Where clause.
> >
> > (The actual syntax of the strWhere clause will vary depending on details
> of
> > your listbox control.)
> >
> > --
> > --Roger Carlson
> > www.rogersaccesslibrary.com
> > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> >
> >
> > "William Wisnieski" <ww********@admissions.umass.edu> wrote in message > > news:3f********@news-1.oit.umass.edu...
> > > Hello Everyone,
> > >
> > > I have a main form with a datasheet subform that I use to query
by form.
> > > After the user selects two criteria on the main form and clicks

the > > > cmdShowResults button on the main form, the subform returns the
records
> > > based on the two criteria. The criteria used on the main form are > values
> > > selected in two list boxes. When the user clicks on the first list box
> > > (lstCollege), it returns values in the second list box (lstAcadPlan) > based
> > > on the first. The user then clicks on the cmdShowResults to filter and
> > > return records in the datasheet subform. This works fine except for one
> > > problem. Both list boxes are set up for single select values--I now > need
> > to
> > > make the second list box (lstAcadPlan) a multi-select list box and pass
> > the
> > > values to the filter. I have no idea how to include that in my code and
> > was
> > > wondering if anyone had any ideas on what I should do. Here is the code
> I
> > > have so far that works fine as long as only one value is
selected in the
> > > second list box:
> > >
> > > Private Sub cmdShowResults_Click()
> > > Me.sfrmSearchResults.Visible = True
> > > Me.lblSubformInstructions.Visible = True
> > > Dim strWhere As String
> > > Dim rst As Recordset
> > >
> > > If Len(Me.lstCollege & "") > 0 Then
> > > strWhere = "[College] = '" & Me.lstCollege & "'"
> > > End If
> > >
> > > If Len(Me.lstAcadPlan & "") > 0 Then
> > > If Len(strWhere) > 0 Then
> > > strWhere = strWhere & "And [AcadPlan] = '" &
Me.lstAcadPlan
> &
> > > "'"
> > > Else
> > > strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
> > > End If
> > > End If
> > > Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
> > > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> > > End Sub
> > >
> > > Thank you,
> > >
> > > William
> > >
> > >
> >
> >
>
>



Nov 12 '05 #11

P: n/a
Whoa, sorry about that....I see now what sections you are referring
to.....I'm working through it right now and will let you know how I make
out.

Thanks again....

William
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Roger....thanks again.

Sorry for the confusion. Regarding the filtering problem, all records are
being returned. Also, I don't quite understand the If Len(strWhere). I'm
not sure which sections you are referring to. Should I be using that
instead of say, If Len(Me.lstCollege & "") > 0 Then.....................

William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
When you say it does not filter, do you mean All records are returned or No
records are returned?

Well, I see some obvious problems, none of which may be the major cause.

1) This section:
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("


should have an AND like so:
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND [AcadPlan] IN ("


2) This section:
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'" Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If

is using two different datatypes. If EMPLID is text, the first should be
used, if it is numeric, the second should be used

3) This is not causing a problem yet, but may in the future. If you have a
person with a Last name of O'Brien, this code will fail:
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
To fix, replace each apostrophe (') with TWO quotes ("")
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = """ & Me.txtLast & """" Else
strWhere = "[Last] = """ & Me.txtLast & """"
End If


Now, as I said, these may not be the cause of the problem at hand. The

best
thing to do in a situation like this is to put the following line:

Debug.Print strWhere

just before the filtering line. Then put a break point on the first

filter
line. The code will stop before the filter executes and if you look in

the
Debug (Immediate) window, you will see exactly what your Where clause

looks
like. You can even cut and paste it into a query and see what it is
acutally returning or what error messages it produces.

HTH

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Thanks Roger. I used your code and it works great. I'm trying to

modify
it
now for two reasons. Now, I have to add some more search criteria

(State, HonorsCollege, EMPLID, LastName) on the main form. Second, I need the

user
to be able to select from the college list without being required to

select
from the AcadPlan list.

Here's what I have that works so far:

Private Sub cmdShowResults_Click()
Me.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True

==================================

Now here is what I have added. The good news is it doesn't return any
errors. The bad news is it doesn't filter any records:

Private Sub cmdShowResults_Click()
Me.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If

If Len(Me.lstHonors & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [HonorsCollege] = '" & Me.lstHonors
&
"'"
Else
strWhere = "[HonorsCollege] = '" & Me.lstHonors & "'"
End If

End If
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'" Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

If Len(Me.txtEMPLID & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID &

"'" Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If
End If

If Len(Me.txtLast & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
End If

Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True

Thanks again for your help. It is much appreciated.

William



"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:uc**************@TK2MSFTNGP09.phx.gbl...
> OK. Sorry, I missed a bit. Try this:
>
> strWhere = "Where [College] = '" & Me.lstCollege & "' and "
> strWhere = strWhere & "[AcadPlan] IN ("
> For i = 0 To lstAcadPlan.ListCount - 1
> If lstAcadPlan.Selected(i) Then
> strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', " > End If
> Next i
> strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
>
> --Roger Carlson
> www.rogersaccesslibrary.com
> Reply to: Roger dot Carlson at Spectrum-Health dot Org
>
> "William Wisnieski" <ww********@admissions.umass.edu> wrote in message > news:3f********@news-1.oit.umass.edu...
> > Thanks Roger,
> >
> > I tried your code. I get a run time error and when I go to debug, it > seems
> > to have combined the college and academic plan. For example, strWhere > > filter should show "[College] = CAS and [AcadPlan] = Art, Or

Philosophy,
> Or
> > History". But it is showing [College]=CAS, Art, Philosophy, History." > >
> > William
> >
> >
> > "Roger Carlson" <NO**************@hotmail.com> wrote in message
> > news:O7****************@tk2msftngp13.phx.gbl...
> > > On my website (see sig below) is a small sample database called
> > > "CreateQueries2.mdb". Form 6 in this database illustrates how to create
> a
> > > Where condition in code with a multi-select listbox. See the code > behind
> > > the form for details. In broad outline, it will work something like > this:
> > >
> > > For i = 0 To lstAcadPlan.ListCount - 1
> > > If lstAcadPlan.Selected(i) Then
> > > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
> > > End If
> > > Next i
> > > strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
> > >
> > > This code loops through the values in the listbox. When it reaches one
> > that
> > > is selected, it adds to the Where clause. When it reaches the end,
it
> > lops
> > > the last comma off of the Where clause.
> > >
> > > (The actual syntax of the strWhere clause will vary depending on
details
> > of
> > > your listbox control.)
> > >
> > > --
> > > --Roger Carlson
> > > www.rogersaccesslibrary.com
> > > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> > >
> > >
> > > "William Wisnieski" <ww********@admissions.umass.edu> wrote in message
> > > news:3f********@news-1.oit.umass.edu...
> > > > Hello Everyone,
> > > >
> > > > I have a main form with a datasheet subform that I use to

query by > form.
> > > > After the user selects two criteria on the main form and
clicks the
> > > > cmdShowResults button on the main form, the subform returns
the > records
> > > > based on the two criteria. The criteria used on the main form

are > > values
> > > > selected in two list boxes. When the user clicks on the first

list
> box
> > > > (lstCollege), it returns values in the second list box

(lstAcadPlan)
> > based
> > > > on the first. The user then clicks on the cmdShowResults to

filter
> and
> > > > return records in the datasheet subform. This works fine except for
> one
> > > > problem. Both list boxes are set up for single select
values--I
now
> > need
> > > to
> > > > make the second list box (lstAcadPlan) a multi-select list box and > pass
> > > the
> > > > values to the filter. I have no idea how to include that in

my code
> and
> > > was
> > > > wondering if anyone had any ideas on what I should do. Here
is the
> code
> > I
> > > > have so far that works fine as long as only one value is

selected
in
> the
> > > > second list box:
> > > >
> > > > Private Sub cmdShowResults_Click()
> > > > Me.sfrmSearchResults.Visible = True
> > > > Me.lblSubformInstructions.Visible = True
> > > > Dim strWhere As String
> > > > Dim rst As Recordset
> > > >
> > > > If Len(Me.lstCollege & "") > 0 Then
> > > > strWhere = "[College] = '" & Me.lstCollege & "'"
> > > > End If
> > > >
> > > > If Len(Me.lstAcadPlan & "") > 0 Then
> > > > If Len(strWhere) > 0 Then
> > > > strWhere = strWhere & "And [AcadPlan] = '" &
> Me.lstAcadPlan
> > &
> > > > "'"
> > > > Else
> > > > strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
> > > > End If
> > > > End If
> > > > Forms(Me.Name)("sfrmSearchResults").Form.Filter =

strWhere > > > > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> > > > End Sub
> > > >
> > > > Thank you,
> > > >
> > > > William
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 12 '05 #12

P: n/a
Put the Debug line at the bottom of the procedure just above:
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere

Where you have it now displays nothing because strWhere has not yet been set
to anything. You can also move the line around to different points in the
procedure to see what the Where clause looks like at each point. This is
only used for development, however and should be commented out for
production as it uses up processing time.
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu... Roger,

The debug idea sounds great. I'm trying it right now with no luck though.
nothing shows up in the immediate window. This is what I have:

If Len(Me.lstCollege & "") > 0 Then
Debug.Print strWhere
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

I have set a breakpoint at strWhere = "[College] = '" & Me.lstCollege & "'" and opened the immediate window. I select the first two criteria on the
form and click the show results button and the line strWhere = "[College] = '" & Me.lstCollege & "'" gets highlighted in yellow but nothing shows up
in the immediate window.

Sorry for my numerous posts trying to get this thing done!

William


"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Whoa, sorry about that....I see now what sections you are referring
to.....I'm working through it right now and will let you know how I make
out.

Thanks again....

William
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Roger....thanks again.

Sorry for the confusion. Regarding the filtering problem, all records are being returned. Also, I don't quite understand the If Len(strWhere). I'm not sure which sections you are referring to. Should I be using that
instead of say, If Len(Me.lstCollege & "") > 0 Then.....................
William
"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
> When you say it does not filter, do you mean All records are returned
or
No
> records are returned?
>
> Well, I see some obvious problems, none of which may be the major cause. >
> 1) This section:
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "[AcadPlan] IN ("
>
> should have an AND like so:
>
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & " AND [AcadPlan] IN ("
>
> 2) This section:
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [EMPLID] = '" &
Me.txtEMPLID
& "'"
> > Else
> > strWhere = "[EMPLID] = " & Me.txtEMPLID
> >
> > End If
> is using two different datatypes. If EMPLID is text, the first
should
be
> used, if it is numeric, the second should be used
>
> 3) This is not causing a problem yet, but may in the future. If you have
a
> person with a Last name of O'Brien, this code will fail:
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'" > > Else
> > strWhere = "[Last] = '" & Me.txtLast & "'"
> > End If
>
> To fix, replace each apostrophe (') with TWO quotes ("")
>
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [Last] = """ & Me.txtLast &

""""
> > Else
> > strWhere = "[Last] = """ & Me.txtLast & """"
> > End If
>
> Now, as I said, these may not be the cause of the problem at hand. The best
> thing to do in a situation like this is to put the following line:
>
> Debug.Print strWhere
>
> just before the filtering line. Then put a break point on the first
filter
> line. The code will stop before the filter executes and if you look in the
> Debug (Immediate) window, you will see exactly what your Where
clause looks
> like. You can even cut and paste it into a query and see what it is
> acutally returning or what error messages it produces.
>
> HTH
>
> --
> --Roger Carlson
> www.rogersaccesslibrary.com
> Reply to: Roger dot Carlson at Spectrum-Health dot Org
>
> "William Wisnieski" <ww********@admissions.umass.edu> wrote in message > news:3f********@news-1.oit.umass.edu...
> > Thanks Roger. I used your code and it works great. I'm trying to
modify
> it
> > now for two reasons. Now, I have to add some more search criteria
(State,
> > HonorsCollege, EMPLID, LastName) on the main form. Second, I need

the > user
> > to be able to select from the college list without being required to > select
> > from the AcadPlan list.
> >
> > Here's what I have that works so far:
> >
> > Private Sub cmdShowResults_Click()
> > Me.sfrmRoundUpSearchResults.Visible = True
> > Me.lblSubformInstructions.Visible = True
> > Dim strWhere As String
> > Dim rst As Recordset
> >
> > strWhere = strWhere & "[AcadPlan] IN ("
> > For i = 0 To lstAcadPlan.ListCount - 1
> > If lstAcadPlan.Selected(i) Then
> > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', " > > End If
> > Next i
> > strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
> >
> > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere > > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True > >
> > ==================================
> >
> > Now here is what I have added. The good news is it doesn't return any > > errors. The bad news is it doesn't filter any records:
> >
> > Private Sub cmdShowResults_Click()
> > Me.sfrmRoundUpSearchResults.Visible = True
> > Me.lblSubformInstructions.Visible = True
> > Dim strWhere As String
> > Dim rst As Recordset
> >
> > If Len(Me.lstCollege & "") > 0 Then
> > strWhere = "[College] = '" & Me.lstCollege & "'"
> > End If
> >
> > If Len(Me.lstAcadPlan & "") > 0 Then
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "[AcadPlan] IN ("
> > For i = 0 To lstAcadPlan.ListCount - 1
> > If lstAcadPlan.Selected(i) Then
> > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', " > > End If
> > Next i
> > strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
> > Else
> > strWhere = "[AcadPlan] IN ("
> > For i = 0 To lstAcadPlan.ListCount - 1
> > If lstAcadPlan.Selected(i) Then
> > strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
> > End If
> > Next i
> > strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
> > End If
> >
> > If Len(Me.lstHonors & "") > 0 Then
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [HonorsCollege] = '" &
Me.lstHonors
> &
> > "'"
> > Else
> > strWhere = "[HonorsCollege] = '" & Me.lstHonors & "'"
> > End If
> >
> > End If
> > If Len(Me.cboState & "") > 0 Then
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
> > Else
> > strWhere = "[State] = '" & Me.cboState & "'"
> > End If
> > End If
> >
> > If Len(Me.txtEMPLID & "") > 0 Then
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [EMPLID] = '" &
Me.txtEMPLID & "'"
> > Else
> > strWhere = "[EMPLID] = " & Me.txtEMPLID
> >
> > End If
> > End If
> >
> > If Len(Me.txtLast & "") > 0 Then
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'" > > Else
> > strWhere = "[Last] = '" & Me.txtLast & "'"
> > End If
> > End If
> >
> > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere > > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn =
True > >
> > Thanks again for your help. It is much appreciated.
> >
> > William
> >
> >
> >
> >
> >
> >
> >
> > "Roger Carlson" <NO**************@hotmail.com> wrote in message
> > news:uc**************@TK2MSFTNGP09.phx.gbl...
> > > OK. Sorry, I missed a bit. Try this:
> > >
> > > strWhere = "Where [College] = '" & Me.lstCollege & "' and "
> > > strWhere = strWhere & "[AcadPlan] IN ("
> > > For i = 0 To lstAcadPlan.ListCount - 1
> > > If lstAcadPlan.Selected(i) Then
> > > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "',
"
> > > End If
> > > Next i
> > > strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
> > >
> > > --Roger Carlson
> > > www.rogersaccesslibrary.com
> > > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> > >
> > > "William Wisnieski" <ww********@admissions.umass.edu> wrote in message
> > > news:3f********@news-1.oit.umass.edu...
> > > > Thanks Roger,
> > > >
> > > > I tried your code. I get a run time error and when I go to

debug, it
> > > seems
> > > > to have combined the college and academic plan. For example,
strWhere
> > > > filter should show "[College] = CAS and [AcadPlan] = Art, Or
> Philosophy,
> > > Or
> > > > History". But it is showing [College]=CAS, Art, Philosophy,
History."
> > > >
> > > > William
> > > >
> > > >
> > > > "Roger Carlson" <NO**************@hotmail.com> wrote in message > > > > news:O7****************@tk2msftngp13.phx.gbl...
> > > > > On my website (see sig below) is a small sample database called > > > > > "CreateQueries2.mdb". Form 6 in this database illustrates how to
> > create
> > > a
> > > > > Where condition in code with a multi-select listbox. See
the
code
> > > behind
> > > > > the form for details. In broad outline, it will work something like
> > > this:
> > > > >
> > > > > For i = 0 To lstAcadPlan.ListCount - 1
> > > > > If lstAcadPlan.Selected(i) Then
> > > > > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i)
& "',
"
> > > > > End If
> > > > > Next i
> > > > > strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
> > > > >
> > > > > This code loops through the values in the listbox. When it
reaches
> > one
> > > > that
> > > > > is selected, it adds to the Where clause. When it reaches
the end,
> it
> > > > lops
> > > > > the last comma off of the Where clause.
> > > > >
> > > > > (The actual syntax of the strWhere clause will vary depending on > > details
> > > > of
> > > > > your listbox control.)
> > > > >
> > > > > --
> > > > > --Roger Carlson
> > > > > www.rogersaccesslibrary.com
> > > > > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> > > > >
> > > > >
> > > > > "William Wisnieski" <ww********@admissions.umass.edu> wrote
in > message
> > > > > news:3f********@news-1.oit.umass.edu...
> > > > > > Hello Everyone,
> > > > > >
> > > > > > I have a main form with a datasheet subform that I use to

query
by
> > > form.
> > > > > > After the user selects two criteria on the main form and

clicks
> the
> > > > > > cmdShowResults button on the main form, the subform returns the
> > > records
> > > > > > based on the two criteria. The criteria used on the main form are
> > > > values
> > > > > > selected in two list boxes. When the user clicks on the first > list
> > > box
> > > > > > (lstCollege), it returns values in the second list box
> (lstAcadPlan)
> > > > based
> > > > > > on the first. The user then clicks on the cmdShowResults
to > filter
> > > and
> > > > > > return records in the datasheet subform. This works fine

except
> for
> > > one
> > > > > > problem. Both list boxes are set up for single select

values--I
> now
> > > > need
> > > > > to
> > > > > > make the second list box (lstAcadPlan) a multi-select list

box and
> > > pass
> > > > > the
> > > > > > values to the filter. I have no idea how to include that
in my
> code
> > > and
> > > > > was
> > > > > > wondering if anyone had any ideas on what I should do.
Here is
> the
> > > code
> > > > I
> > > > > > have so far that works fine as long as only one value is
selected
> in
> > > the
> > > > > > second list box:
> > > > > >
> > > > > > Private Sub cmdShowResults_Click()
> > > > > > Me.sfrmSearchResults.Visible = True
> > > > > > Me.lblSubformInstructions.Visible = True
> > > > > > Dim strWhere As String
> > > > > > Dim rst As Recordset
> > > > > >
> > > > > > If Len(Me.lstCollege & "") > 0 Then
> > > > > > strWhere = "[College] = '" & Me.lstCollege & "'"
> > > > > > End If
> > > > > >
> > > > > > If Len(Me.lstAcadPlan & "") > 0 Then
> > > > > > If Len(strWhere) > 0 Then
> > > > > > strWhere = strWhere & "And [AcadPlan] = '" &
> > > Me.lstAcadPlan
> > > > &
> > > > > > "'"
> > > > > > Else
> > > > > > strWhere = "[AcadPlan] = '" & Me.lstAcadPlan &

"'" > > > > > > End If
> > > > > > End If
> > > > > > Forms(Me.Name)("sfrmSearchResults").Form.Filter =

strWhere
> > > > > > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True > > > > > > End Sub
> > > > > >
> > > > > > Thank you,
> > > > > >
> > > > > > William
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 12 '05 #13

P: n/a
Hello Again,

I finally was able to play around with the debug feature and can at least
see results. Here are the ongoing problems I'm having.

1. I can't get the results from the lstAcadPlan list box selection to
concatenate with other criteria (I get a run time error).
2. If I only want to choose an item from one of the criteria (lstCollege
for example), I also get a run time error.

When I use just the following code by itself, it works--as long I select one
item from the lstCollege list box and any number of items from the
lstAcadPlan list box.

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
Debug.Print strWhere
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Here is what is displayed in the debug window (I selected three academic
plans):

[AcadPlan] IN ('BA-AFROAM',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART',
If I add the following to the code above and select the AcadPlan AND State,
I get a runtime error 2448 (can't assign a value to this object). If I
select just the State and I get the same run time error.

If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Debug.Print strWhere
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

Here is what then gets displayed in the debug Window

[AcadPlan] IN)

It should be returning

[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART', AND [State] = 'MA'

Thanks for your help...

William



"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:%2******************@TK2MSFTNGP10.phx.gbl...
When you say it does not filter, do you mean All records are returned or No records are returned?

Well, I see some obvious problems, none of which may be the major cause.

1) This section:
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
should have an AND like so:
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND [AcadPlan] IN ("


2) This section:
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If

is using two different datatypes. If EMPLID is text, the first should be
used, if it is numeric, the second should be used

3) This is not causing a problem yet, but may in the future. If you have

a person with a Last name of O'Brien, this code will fail:
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
To fix, replace each apostrophe (') with TWO quotes ("")
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = """ & Me.txtLast & """"
Else
strWhere = "[Last] = """ & Me.txtLast & """"
End If


Now, as I said, these may not be the cause of the problem at hand. The

best thing to do in a situation like this is to put the following line:

Debug.Print strWhere

just before the filtering line. Then put a break point on the first filter line. The code will stop before the filter executes and if you look in the Debug (Immediate) window, you will see exactly what your Where clause looks like. You can even cut and paste it into a query and see what it is
acutally returning or what error messages it produces.

HTH

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
Thanks Roger. I used your code and it works great. I'm trying to modify
it
now for two reasons. Now, I have to add some more search criteria
(State, HonorsCollege, EMPLID, LastName) on the main form. Second, I need the

user
to be able to select from the college list without being required to

select
from the AcadPlan list.

Here's what I have that works so far:

Private Sub cmdShowResults_Click()
Me.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True

==================================

Now here is what I have added. The good news is it doesn't return any
errors. The bad news is it doesn't filter any records:

Private Sub cmdShowResults_Click()
Me.sfrmRoundUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Dim strWhere As String
Dim rst As Recordset

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Len(Me.lstAcadPlan & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If

If Len(Me.lstHonors & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [HonorsCollege] = '" & Me.lstHonors &
"'"
Else
strWhere = "[HonorsCollege] = '" & Me.lstHonors & "'"
End If

End If
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

If Len(Me.txtEMPLID & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID &
"'" Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If
End If

If Len(Me.txtLast & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
End If

Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn = True

Thanks again for your help. It is much appreciated.

William



"Roger Carlson" <NO**************@hotmail.com> wrote in message
news:uc**************@TK2MSFTNGP09.phx.gbl...
OK. Sorry, I missed a bit. Try this:

strWhere = "Where [College] = '" & Me.lstCollege & "' and "
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"

--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"William Wisnieski" <ww********@admissions.umass.edu> wrote in message
news:3f********@news-1.oit.umass.edu...
> Thanks Roger,
>
> I tried your code. I get a run time error and when I go to debug, it seems
> to have combined the college and academic plan. For example, strWhere > filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy, Or
> History". But it is showing [College]=CAS, Art, Philosophy, History." >
> William
>
>
> "Roger Carlson" <NO**************@hotmail.com> wrote in message
> news:O7****************@tk2msftngp13.phx.gbl...
> > On my website (see sig below) is a small sample database called
> > "CreateQueries2.mdb". Form 6 in this database illustrates how to

create
a
> > Where condition in code with a multi-select listbox. See the code
behind
> > the form for details. In broad outline, it will work something like this:
> >
> > For i = 0 To lstAcadPlan.ListCount - 1
> > If lstAcadPlan.Selected(i) Then
> > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', " > > End If
> > Next i
> > strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
> >
> > This code loops through the values in the listbox. When it reaches one
> that
> > is selected, it adds to the Where clause. When it reaches the
end, it > lops
> > the last comma off of the Where clause.
> >
> > (The actual syntax of the strWhere clause will vary depending on details
> of
> > your listbox control.)
> >
> > --
> > --Roger Carlson
> > www.rogersaccesslibrary.com
> > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> >
> >
> > "William Wisnieski" <ww********@admissions.umass.edu> wrote in message > > news:3f********@news-1.oit.umass.edu...
> > > Hello Everyone,
> > >
> > > I have a main form with a datasheet subform that I use to query
by form.
> > > After the user selects two criteria on the main form and clicks

the > > > cmdShowResults button on the main form, the subform returns the
records
> > > based on the two criteria. The criteria used on the main form are > values
> > > selected in two list boxes. When the user clicks on the first list box
> > > (lstCollege), it returns values in the second list box (lstAcadPlan) > based
> > > on the first. The user then clicks on the cmdShowResults to filter and
> > > return records in the datasheet subform. This works fine except for one
> > > problem. Both list boxes are set up for single select values--I now > need
> > to
> > > make the second list box (lstAcadPlan) a multi-select list box and pass
> > the
> > > values to the filter. I have no idea how to include that in my code and
> > was
> > > wondering if anyone had any ideas on what I should do. Here is the code
> I
> > > have so far that works fine as long as only one value is
selected in the
> > > second list box:
> > >
> > > Private Sub cmdShowResults_Click()
> > > Me.sfrmSearchResults.Visible = True
> > > Me.lblSubformInstructions.Visible = True
> > > Dim strWhere As String
> > > Dim rst As Recordset
> > >
> > > If Len(Me.lstCollege & "") > 0 Then
> > > strWhere = "[College] = '" & Me.lstCollege & "'"
> > > End If
> > >
> > > If Len(Me.lstAcadPlan & "") > 0 Then
> > > If Len(strWhere) > 0 Then
> > > strWhere = strWhere & "And [AcadPlan] = '" &
Me.lstAcadPlan
> &
> > > "'"
> > > Else
> > > strWhere = "[AcadPlan] = '" & Me.lstAcadPlan & "'"
> > > End If
> > > End If
> > > Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
> > > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> > > End Sub
> > >
> > > Thank you,
> > >
> > > William
> > >
> > >
> >
> >
>
>



Nov 12 '05 #14

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Actually, you should get a return string like this:

[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART') AND [State] = 'MA'

The AND State = 'MA' should be outside the closing parenthesis of the
IN() clause. You're neglecting to put the closing parenthesis after
the last selected item in the ListBox.

Here is a function I created to get items from ListBoxes (Acc97):

Under the Case dbText, if using Access 2000 & greater use:

Replace(lst.ItemData(row), "'", "''")

instead of

ReplaceStr(lst.ItemData(row), "'", "''", 0)
Public Function getIDs(lst As Control, ByVal intType As Integer) _
As String
' Purpose:
' Get a list of the item IDs into a comma separated string
' In:
' lst A ref to a list box control
' intType One of the dbText, dbDate, dbTime, dbNumeric
' Out:
' A string of comma-delimited IDs. Format: "1,2,3,4"
' If the intType is undefined an empty string is returned.
' Created:
' mgf 8mar2000
' Modified:
' mgf 10mar2000 Added intType selection
'

Dim row As Variant
For Each row In lst.ItemsSelected
Dim strIDs As String
Select Case intType
Case dbText
strIDs = strIDs & "'" & _
ReplaceStr(lst.ItemData(row), "'", "''", 0) & "',"
Case dbDate, dbTime
' Assumes USA date format mm/dd/yyyy
strIDs = strIDs & "#" & lst.ItemData(row) & "#,"
Case dbNumeric
strIDs = strIDs & lst.ItemData(row) & ","
Case Else
' Don't know how to handle this type
Exit Function
End Select
Next row

' Return string w/o trailing comma
getIDs = Left$(strIDs, Len(strIDs) - 1)

End Function

It is called this way:

' Check if any data in ListBox
If lstAvailable.ItemsSelected.Count > 0 Then
Dim strResult As String
' Get selected items IDs
strResult = getIDs(lstAvailable, dbNumeric)
strWhere = " Items In (" & strResult & ") "
End If

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP/DRu4echKqOuFEgEQK3vQCfTTmCDIhyH1TvV4ITHjDX9M4WdawA n3f2
QZR+Xw0zx0fkgGb3ikptpoSA
=S6v1
-----END PGP SIGNATURE-----
William Wisnieski wrote:
Hello Again,

I finally was able to play around with the debug feature and can at least
see results. Here are the ongoing problems I'm having.

1. I can't get the results from the lstAcadPlan list box selection to
concatenate with other criteria (I get a run time error).
2. If I only want to choose an item from one of the criteria (lstCollege
for example), I also get a run time error.

When I use just the following code by itself, it works--as long I select one
item from the lstCollege list box and any number of items from the
lstAcadPlan list box.

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
Debug.Print strWhere
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Here is what is displayed in the debug window (I selected three academic
plans):

[AcadPlan] IN ('BA-AFROAM',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART',
If I add the following to the code above and select the AcadPlan AND State,
I get a runtime error 2448 (can't assign a value to this object). If I
select just the State and I get the same run time error.

If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Debug.Print strWhere
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

Here is what then gets displayed in the debug Window

[AcadPlan] IN)

It should be returning

[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART', AND [State] = 'MA'

Thanks for your help...

William


Nov 12 '05 #15

P: n/a
Why don't you go one step further with all the excellent help and put
the code to generate the IN clause in a reusable function?

eg
Public Function GetListBoxValues(byref plst as Listbox,
Optional Byref pblnIsString as Boolean) as string
' IN : plst - a multi select list box
' pblnIsString 0 - '[value]' if True, [Value] if False
' OUT: A string representing the innards of your IN Clause
'
your previous code...(with a few mods)

End Function

usage

Function BuildWhereClause() as string

dim strWhere as String

If me.lstWhatever.ItemsSelected.Count > 0 then
if Len(strWhere) > 0 then strWhere = strWhere & " AND "
strWhere = strWhere & "SomeField IN(" &
GetListBoxValues(me.lstWhatever, True) & ")"
end if

etc

BuildWhereClause = strWhere

End Function

Just would seem to me to make the whole thing a little more reusable
and flexible....

Peter
Nov 12 '05 #16

P: n/a
Thanks for everyone's generous help on this....still trying to work through
it.

MG, I've tried putting a closing parentheses at the end of the line but it
won't let me....

I'm trying to change this:

strWhere = strWhere & "[AcadPlan] IN ("

to this: strWhere = strWhere & "[AcadPlan] IN (")

But I get an error message that says, "expected end of statement. Where
should the closing parentheses be?

Here's all of my code again:

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
Debug.Print strWhere
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Thanks,

William


"MGFoster" <me@privacy.com> wrote in message
news:Xk*******************@newsread1.news.pas.eart hlink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Actually, you should get a return string like this:

[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART') AND [State] = 'MA'

The AND State = 'MA' should be outside the closing parenthesis of the
IN() clause. You're neglecting to put the closing parenthesis after
the last selected item in the ListBox.

Here is a function I created to get items from ListBoxes (Acc97):

Under the Case dbText, if using Access 2000 & greater use:

Replace(lst.ItemData(row), "'", "''")

instead of

ReplaceStr(lst.ItemData(row), "'", "''", 0)
Public Function getIDs(lst As Control, ByVal intType As Integer) _
As String
' Purpose:
' Get a list of the item IDs into a comma separated string
' In:
' lst A ref to a list box control
' intType One of the dbText, dbDate, dbTime, dbNumeric
' Out:
' A string of comma-delimited IDs. Format: "1,2,3,4"
' If the intType is undefined an empty string is returned.
' Created:
' mgf 8mar2000
' Modified:
' mgf 10mar2000 Added intType selection
'

Dim row As Variant
For Each row In lst.ItemsSelected
Dim strIDs As String
Select Case intType
Case dbText
strIDs = strIDs & "'" & _
ReplaceStr(lst.ItemData(row), "'", "''", 0) & "',"
Case dbDate, dbTime
' Assumes USA date format mm/dd/yyyy
strIDs = strIDs & "#" & lst.ItemData(row) & "#,"
Case dbNumeric
strIDs = strIDs & lst.ItemData(row) & ","
Case Else
' Don't know how to handle this type
Exit Function
End Select
Next row

' Return string w/o trailing comma
getIDs = Left$(strIDs, Len(strIDs) - 1)

End Function

It is called this way:

' Check if any data in ListBox
If lstAvailable.ItemsSelected.Count > 0 Then
Dim strResult As String
' Get selected items IDs
strResult = getIDs(lstAvailable, dbNumeric)
strWhere = " Items In (" & strResult & ") "
End If

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP/DRu4echKqOuFEgEQK3vQCfTTmCDIhyH1TvV4ITHjDX9M4WdawA n3f2
QZR+Xw0zx0fkgGb3ikptpoSA
=S6v1
-----END PGP SIGNATURE-----
William Wisnieski wrote:
Hello Again,

I finally was able to play around with the debug feature and can at least see results. Here are the ongoing problems I'm having.

1. I can't get the results from the lstAcadPlan list box selection to
concatenate with other criteria (I get a run time error).
2. If I only want to choose an item from one of the criteria (lstCollege for example), I also get a run time error.

When I use just the following code by itself, it works--as long I select one item from the lstCollege list box and any number of items from the
lstAcadPlan list box.

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
Debug.Print strWhere
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Here is what is displayed in the debug window (I selected three academic
plans):

[AcadPlan] IN ('BA-AFROAM',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH',
[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART',
If I add the following to the code above and select the AcadPlan AND State, I get a runtime error 2448 (can't assign a value to this object). If I
select just the State and I get the same run time error.

If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Debug.Print strWhere
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

Here is what then gets displayed in the debug Window

[AcadPlan] IN)

It should be returning

[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART', AND [State] = 'MA'

Thanks for your help...

William

Nov 12 '05 #17

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The In() clause should be formatted like this (string items):

IN ('item2', 'item2', 'item3')

The closing parenthesis is after 'item3' - this indicates that the
list (the values inside the parentheses) is ended.

Your post indicates you don't understand how to concatenate strings in
VBA, but your For...Next loop looks like it should produce a correct
IN() clause - IF any items are selected; otherwise, you'll have a
Where string like this:

[AcadPlan] IN () -- an empty list, which causes an error.

You can check for selected items by:

If Me!lstAcadPlan.ItemsSelected.Count > 0 Then
' There are selected items
... etc. ...

You could put a break-point on:

strWhere = Left(strWhere, Len(strWhere) - 2) & ") "

step thru it (F8) to the next command and print the value of the
variable strWhere in the debug window:

? strWhere

to see if it is formatted correctly.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP/JQY4echKqOuFEgEQJ18ACgmFxZuxG1Nq05OX51Zb+vUXseRVQA oLAO
VwMh3FCvvLN9HDc5uI6guZP0
=i+dA
-----END PGP SIGNATURE-----
William Wisnieski wrote:
Thanks for everyone's generous help on this....still trying to work through
it.

MG, I've tried putting a closing parentheses at the end of the line but it
won't let me....

I'm trying to change this:

strWhere = strWhere & "[AcadPlan] IN ("

to this: strWhere = strWhere & "[AcadPlan] IN (")

But I get an error message that says, "expected end of statement. Where
should the closing parentheses be?

Here's all of my code again:

strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
Debug.Print strWhere
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

Thanks,

William


"MGFoster" <me@privacy.com> wrote in message
news:Xk*******************@newsread1.news.pas.eart hlink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Actually, you should get a return string like this:

[AcadPlan] IN ('BA-AFROAM', 'BA-ANTH', 'BA-ART') AND [State] = 'MA'

The AND State = 'MA' should be outside the closing parenthesis of the
IN() clause. You're neglecting to put the closing parenthesis after
the last selected item in the ListBox.

Here is a function I created to get items from ListBoxes (Acc97):

Under the Case dbText, if using Access 2000 & greater use:

Replace(lst.ItemData(row), "'", "''")

instead of

ReplaceStr(lst.ItemData(row), "'", "''", 0)
Public Function getIDs(lst As Control, ByVal intType As Integer) _
As String
' Purpose:
' Get a list of the item IDs into a comma separated string
' In:
' lst A ref to a list box control
' intType One of the dbText, dbDate, dbTime, dbNumeric
' Out:
' A string of comma-delimited IDs. Format: "1,2,3,4"
' If the intType is undefined an empty string is returned.
' Created:
' mgf 8mar2000
' Modified:
' mgf 10mar2000 Added intType selection
'

Dim row As Variant
For Each row In lst.ItemsSelected
Dim strIDs As String
Select Case intType
Case dbText
strIDs = strIDs & "'" & _
ReplaceStr(lst.ItemData(row), "'", "''", 0) & "',"
Case dbDate, dbTime
' Assumes USA date format mm/dd/yyyy
strIDs = strIDs & "#" & lst.ItemData(row) & "#,"
Case dbNumeric
strIDs = strIDs & lst.ItemData(row) & ","
Case Else
' Don't know how to handle this type
Exit Function
End Select
Next row

' Return string w/o trailing comma
getIDs = Left$(strIDs, Len(strIDs) - 1)

End Function

It is called this way:

' Check if any data in ListBox
If lstAvailable.ItemsSelected.Count > 0 Then
Dim strResult As String
' Get selected items IDs
strResult = getIDs(lstAvailable, dbNumeric)
strWhere = " Items In (" & strResult & ") "
End If

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP/DRu4echKqOuFEgEQK3vQCfTTmCDIhyH1TvV4ITHjDX9M4WdawA n3f2
QZR+Xw0zx0fkgGb3ikptpoSA
=S6v1
-----END PGP SIGNATURE-----

< snip previous posts >

Nov 12 '05 #18

P: n/a
Finally, thanks to all of your help, I was able to get the form up and
running without any problems. Here is the final code I used behind my Show
Results button:

Private Sub cmdShowResults_Click()

Me.sfrmUpSearchResults.Visible = True
Me.lblSubformInstructions.Visible = True
Me.cmdPrint.Visible = True
Dim rst As Recordset
Dim strWhere As String

If Len(Me.lstCollege & "") > 0 Then
strWhere = "[College] = '" & Me.lstCollege & "'"
End If

If Me!lstAcadPlan.ItemsSelected.Count > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "AND" & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If

If Len(Me.lstHonors & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [HonorsCollege] = '" & Me.lstHonors &
"'"
Else
strWhere = "[HonorsCollege] = '" & Me.lstHonors & "'"
End If

End If
If Len(Me.cboState & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [State] = '" & Me.cboState & "'"
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

If Len(Me.txtEMPLID & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = " & Me.txtEMPLID
Else
strWhere = "[EMPLID] = " & Me.txtEMPLID

End If
End If

If Len(Me.txtLast & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = """ & Me.txtLast & """"
Else
strWhere = "[Last] = """ & Me.txtLast & """"
End If
End If

Forms(Me.Name)("sfrmSearchResults").Form.Filter = strWhere
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True

Set rst = Forms("frmSearch").sfrmSearchResults.Form.Recordse t
If rst.RecordCount = 0 Then
MsgBox "There were no records that matched the criteria. Click OK to
search again.", vbOKOnly, "Search"
Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = False
Me.sfrmSearchResults.Visible = False
Me.lblSubformInstructions.Visible = False
Me.lstCollege = Null
Me.lstAcadPlan = Null
Me.lstHonors = Null
Me.cboState = Null
Me.txtEMPLID = Null
Me.txtLast = Null
End If
End Sub
Thanks again to all of you and this invaluable newsgroup!

William




"Pink Panther" <Pi*************@mail.com> wrote in message
news:ec**************************@posting.google.c om...
Why don't you go one step further with all the excellent help and put
the code to generate the IN clause in a reusable function?

eg
Public Function GetListBoxValues(byref plst as Listbox,
Optional Byref pblnIsString as Boolean) as string
' IN : plst - a multi select list box
' pblnIsString 0 - '[value]' if True, [Value] if False
' OUT: A string representing the innards of your IN Clause
'
your previous code...(with a few mods)

End Function

usage

Function BuildWhereClause() as string

dim strWhere as String

If me.lstWhatever.ItemsSelected.Count > 0 then
if Len(strWhere) > 0 then strWhere = strWhere & " AND "
strWhere = strWhere & "SomeField IN(" &
GetListBoxValues(me.lstWhatever, True) & ")"
end if

etc

BuildWhereClause = strWhere

End Function

Just would seem to me to make the whole thing a little more reusable
and flexible....

Peter

Nov 12 '05 #19

P: n/a
"William Wisnieski" <ww********@admissions.umass.edu> wrote in message news:<3f********@news-1.oit.umass.edu>...
Finally, thanks to all of your help, I was able to get the form up and
running without any problems. Here is the final code I used behind my Show
Results button:

Private Sub cmdShowResults_Click() <SNIP> For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = strWhere & "[AcadPlan] IN ("
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If
Well, first off, well done on getting it to work. Now that it works,
it might be time for some tweaking...

Instead of looping through all the items in the listbox and seeing if
they're selected...
For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then


Why not use the ItemsSelected collection of the listbox and just do
the same thing? Won't make much of a difference if you don't have a
ton of items in your listbox, but if you do, it could make your app
run faster...

dim varItem as Variant
For each varItem in lstAcadPlan.ItemsSelected
'do something
Next varItem

there's code that shows how to do this at www.mvps.org/access

look for something like "listbox" + varitem and you'll find an example
of iterating through the list of selected items...

HTH,
Pieter
Nov 12 '05 #20

This discussion thread is closed

Replies have been disabled for this discussion.