Code for Filtering from Mult-select List Box Criteria

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

re: Code for Filtering from Mult-select List Box Criteria

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" <wwisnieski@admissions.umass.edu> wrote in message
news:3fe710b9$1@news-1.oit.umass.edu...[color=blue]
> 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[/color]
to[color=blue]
> make the second list box (lstAcadPlan) a multi-select list box and pass[/color]
the[color=blue]
> values to the filter. I have no idea how to include that in my code and[/color]
was[color=blue]
> 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
>
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
<wwisnieski@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.

[color=blue]
>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
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

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" <tom7744@no.spam.cox.net> wrote in message
news:7n5euvklvuth1m7v909bqbepq9sd2ti3cn@4ax.com...[color=blue]
> On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
> <wwisnieski@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.
>
>[color=green]
> >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)[/color][/color]
based[color=blue][color=green]
> >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[/color][/color]
to[color=blue][color=green]
> >make the second list box (lstAcadPlan) a multi-select list box and pass[/color][/color]
the[color=blue][color=green]
> >values to the filter. I have no idea how to include that in my code and[/color][/color]
was[color=blue][color=green]
> >wondering if anyone had any ideas on what I should do. Here is the code[/color][/color]
I[color=blue][color=green]
> >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
> >[/color]
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

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-carlsoro-SPAM@hotmail.com> wrote in message
news:O7S%23fTKyDHA.3224@tk2msftngp13.phx.gbl...[color=blue]
> 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[/color]
that[color=blue]
> is selected, it adds to the Where clause. When it reaches the end, it[/color]
lops[color=blue]
> the last comma off of the Where clause.
>
> (The actual syntax of the strWhere clause will vary depending on details[/color]
of[color=blue]
> your listbox control.)
>
> --
> --Roger Carlson
> www.rogersaccesslibrary.com
> Reply to: Roger dot Carlson at Spectrum-Health dot Org
>
>
> "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
> news:3fe710b9$1@news-1.oit.umass.edu...[color=green]
> > 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[/color][/color]
values[color=blue][color=green]
> > selected in two list boxes. When the user clicks on the first list box
> > (lstCollege), it returns values in the second list box (lstAcadPlan)[/color][/color]
based[color=blue][color=green]
> > 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[/color][/color]
need[color=blue]
> to[color=green]
> > make the second list box (lstAcadPlan) a multi-select list box and pass[/color]
> the[color=green]
> > values to the filter. I have no idea how to include that in my code and[/color]
> was[color=green]
> > wondering if anyone had any ideas on what I should do. Here is the code[/color][/color]
I[color=blue][color=green]
> > 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[/color][/color]
&[color=blue][color=green]
> > "'"
> > 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
> >
> >[/color]
>
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

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" <wwisnieski@admissions.umass.edu> wrote in message
news:3fe7431e$1@news-1.oit.umass.edu...[color=blue]
> Thanks Roger,
>
> I tried your code. I get a run time error and when I go to debug, it[/color]
seems[color=blue]
> to have combined the college and academic plan. For example, strWhere
> filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy,[/color]
Or[color=blue]
> History". But it is showing [College]=CAS, Art, Philosophy, History."
>
> William
>
>
> "Roger Carlson" <NO-carlsoro-SPAM@hotmail.com> wrote in message
> news:O7S%23fTKyDHA.3224@tk2msftngp13.phx.gbl...[color=green]
> > On my website (see sig below) is a small sample database called
> > "CreateQueries2.mdb". Form 6 in this database illustrates how to create[/color][/color]
a[color=blue][color=green]
> > Where condition in code with a multi-select listbox. See the code[/color][/color]
behind[color=blue][color=green]
> > the form for details. In broad outline, it will work something like[/color][/color]
this:[color=blue][color=green]
> >
> > 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[/color]
> that[color=green]
> > is selected, it adds to the Where clause. When it reaches the end, it[/color]
> lops[color=green]
> > the last comma off of the Where clause.
> >
> > (The actual syntax of the strWhere clause will vary depending on details[/color]
> of[color=green]
> > your listbox control.)
> >
> > --
> > --Roger Carlson
> > www.rogersaccesslibrary.com
> > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> >
> >
> > "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
> > news:3fe710b9$1@news-1.oit.umass.edu...[color=darkred]
> > > Hello Everyone,
> > >
> > > I have a main form with a datasheet subform that I use to query by[/color][/color][/color]
form.[color=blue][color=green][color=darkred]
> > > After the user selects two criteria on the main form and clicks the
> > > cmdShowResults button on the main form, the subform returns the[/color][/color][/color]
records[color=blue][color=green][color=darkred]
> > > based on the two criteria. The criteria used on the main form are[/color][/color]
> values[color=green][color=darkred]
> > > selected in two list boxes. When the user clicks on the first list[/color][/color][/color]
box[color=blue][color=green][color=darkred]
> > > (lstCollege), it returns values in the second list box (lstAcadPlan)[/color][/color]
> based[color=green][color=darkred]
> > > on the first. The user then clicks on the cmdShowResults to filter[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > return records in the datasheet subform. This works fine except for[/color][/color][/color]
one[color=blue][color=green][color=darkred]
> > > problem. Both list boxes are set up for single select values--I now[/color][/color]
> need[color=green]
> > to[color=darkred]
> > > make the second list box (lstAcadPlan) a multi-select list box and[/color][/color][/color]
pass[color=blue][color=green]
> > the[color=darkred]
> > > values to the filter. I have no idea how to include that in my code[/color][/color][/color]
and[color=blue][color=green]
> > was[color=darkred]
> > > wondering if anyone had any ideas on what I should do. Here is the[/color][/color][/color]
code[color=blue]
> I[color=green][color=darkred]
> > > have so far that works fine as long as only one value is selected in[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > 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] = '" &[/color][/color][/color]
Me.lstAcadPlan[color=blue]
> &[color=green][color=darkred]
> > > "'"
> > > 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
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

On Mon, 22 Dec 2003 14:11:52 -0500, "William Wisnieski"
<wwisnieski@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.

[color=blue]
>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" <tom7744@no.spam.cox.net> wrote in message
>news:7n5euvklvuth1m7v909bqbepq9sd2ti3cn@4ax.com.. .[color=green]
>> On Mon, 22 Dec 2003 10:41:45 -0500, "William Wisnieski"
>> <wwisnieski@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.
>>
>>[color=darkred]
>> >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)[/color][/color]
>based[color=green][color=darkred]
>> >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[/color][/color]
>to[color=green][color=darkred]
>> >make the second list box (lstAcadPlan) a multi-select list box and pass[/color][/color]
>the[color=green][color=darkred]
>> >values to the filter. I have no idea how to include that in my code and[/color][/color]
>was[color=green][color=darkred]
>> >wondering if anyone had any ideas on what I should do. Here is the code[/color][/color]
>I[color=green][color=darkred]
>> >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
>> >[/color]
>>[/color]
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

William Wisnieski wrote:
[color=blue]
> 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:[/color]

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

re: Code for Filtering from Mult-select List Box Criteria

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-carlsoro-SPAM@hotmail.com> wrote in message
news:ucPBGqMyDHA.2084@TK2MSFTNGP09.phx.gbl...[color=blue]
> 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" <wwisnieski@admissions.umass.edu> wrote in message
> news:3fe7431e$1@news-1.oit.umass.edu...[color=green]
> > Thanks Roger,
> >
> > I tried your code. I get a run time error and when I go to debug, it[/color]
> seems[color=green]
> > to have combined the college and academic plan. For example, strWhere
> > filter should show "[College] = CAS and [AcadPlan] = Art, Or Philosophy,[/color]
> Or[color=green]
> > History". But it is showing [College]=CAS, Art, Philosophy, History."
> >
> > William
> >
> >
> > "Roger Carlson" <NO-carlsoro-SPAM@hotmail.com> wrote in message
> > news:O7S%23fTKyDHA.3224@tk2msftngp13.phx.gbl...[color=darkred]
> > > On my website (see sig below) is a small sample database called
> > > "CreateQueries2.mdb". Form 6 in this database illustrates how to[/color][/color][/color]
create[color=blue]
> a[color=green][color=darkred]
> > > Where condition in code with a multi-select listbox. See the code[/color][/color]
> behind[color=green][color=darkred]
> > > the form for details. In broad outline, it will work something like[/color][/color]
> this:[color=green][color=darkred]
> > >
> > > 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[/color][/color][/color]
one[color=blue][color=green]
> > that[color=darkred]
> > > is selected, it adds to the Where clause. When it reaches the end, it[/color]
> > lops[color=darkred]
> > > the last comma off of the Where clause.
> > >
> > > (The actual syntax of the strWhere clause will vary depending on[/color][/color][/color]
details[color=blue][color=green]
> > of[color=darkred]
> > > your listbox control.)
> > >
> > > --
> > > --Roger Carlson
> > > www.rogersaccesslibrary.com
> > > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> > >
> > >
> > > "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message
> > > news:3fe710b9$1@news-1.oit.umass.edu...
> > > > Hello Everyone,
> > > >
> > > > I have a main form with a datasheet subform that I use to query by[/color][/color]
> form.[color=green][color=darkred]
> > > > After the user selects two criteria on the main form and clicks the
> > > > cmdShowResults button on the main form, the subform returns the[/color][/color]
> records[color=green][color=darkred]
> > > > based on the two criteria. The criteria used on the main form are[/color]
> > values[color=darkred]
> > > > selected in two list boxes. When the user clicks on the first list[/color][/color]
> box[color=green][color=darkred]
> > > > (lstCollege), it returns values in the second list box (lstAcadPlan)[/color]
> > based[color=darkred]
> > > > on the first. The user then clicks on the cmdShowResults to filter[/color][/color]
> and[color=green][color=darkred]
> > > > return records in the datasheet subform. This works fine except for[/color][/color]
> one[color=green][color=darkred]
> > > > problem. Both list boxes are set up for single select values--I now[/color]
> > need[color=darkred]
> > > to
> > > > make the second list box (lstAcadPlan) a multi-select list box and[/color][/color]
> pass[color=green][color=darkred]
> > > the
> > > > values to the filter. I have no idea how to include that in my code[/color][/color]
> and[color=green][color=darkred]
> > > was
> > > > wondering if anyone had any ideas on what I should do. Here is the[/color][/color]
> code[color=green]
> > I[color=darkred]
> > > > have so far that works fine as long as only one value is selected in[/color][/color]
> the[color=green][color=darkred]
> > > > 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] = '" &[/color][/color]
> Me.lstAcadPlan[color=green]
> > &[color=darkred]
> > > > "'"
> > > > 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
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

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:[color=blue]
> If Len(strWhere) > 0 Then
> strWhere = strWhere & "[AcadPlan] IN ("[/color]

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

2) This section:[color=blue]
> If Len(strWhere) > 0 Then
> strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID & "'"
> Else
> strWhere = "[EMPLID] = " & Me.txtEMPLID
>
> End If[/color]
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:[color=blue]
> If Len(strWhere) > 0 Then
> strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
> Else
> strWhere = "[Last] = '" & Me.txtLast & "'"
> End If[/color]

To fix, replace each apostrophe (') with TWO quotes ("")
[color=blue]
> If Len(strWhere) > 0 Then
> strWhere = strWhere & "And [Last] = """ & Me.txtLast & """"
> Else
> strWhere = "[Last] = """ & Me.txtLast & """"
> End If[/color]

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" <wwisnieski@admissions.umass.edu> wrote in message
news:3fe894e4$1@news-1.oit.umass.edu...[color=blue]
> Thanks Roger. I used your code and it works great. I'm trying to modify[/color]
it[color=blue]
> 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[/color]
user[color=blue]
> to be able to select from the college list without being required to[/color]
select[color=blue]
> 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[/color]
&[color=blue]
> "'"
> 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-carlsoro-SPAM@hotmail.com> wrote in message
> news:ucPBGqMyDHA.2084@TK2MSFTNGP09.phx.gbl...[color=green]
> > 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" <wwisnieski@admissions.umass.edu> wrote in message
> > news:3fe7431e$1@news-1.oit.umass.edu...[color=darkred]
> > > Thanks Roger,
> > >
> > > I tried your code. I get a run time error and when I go to debug, it[/color]
> > seems[color=darkred]
> > > to have combined the college and academic plan. For example, strWhere
> > > filter should show "[College] = CAS and [AcadPlan] = Art, Or[/color][/color][/color]
Philosophy,[color=blue][color=green]
> > Or[color=darkred]
> > > History". But it is showing [College]=CAS, Art, Philosophy, History."
> > >
> > > William
> > >
> > >
> > > "Roger Carlson" <NO-carlsoro-SPAM@hotmail.com> wrote in message
> > > news:O7S%23fTKyDHA.3224@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[/color][/color]
> create[color=green]
> > a[color=darkred]
> > > > Where condition in code with a multi-select listbox. See the code[/color]
> > behind[color=darkred]
> > > > the form for details. In broad outline, it will work something like[/color]
> > this:[color=darkred]
> > > >
> > > > 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[/color][/color]
> one[color=green][color=darkred]
> > > that
> > > > is selected, it adds to the Where clause. When it reaches the end,[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > lops
> > > > the last comma off of the Where clause.
> > > >
> > > > (The actual syntax of the strWhere clause will vary depending on[/color][/color]
> details[color=green][color=darkred]
> > > of
> > > > your listbox control.)
> > > >
> > > > --
> > > > --Roger Carlson
> > > > www.rogersaccesslibrary.com
> > > > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> > > >
> > > >
> > > > "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in[/color][/color][/color]
message[color=blue][color=green][color=darkred]
> > > > news:3fe710b9$1@news-1.oit.umass.edu...
> > > > > Hello Everyone,
> > > > >
> > > > > I have a main form with a datasheet subform that I use to query by[/color]
> > form.[color=darkred]
> > > > > After the user selects two criteria on the main form and clicks[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > > cmdShowResults button on the main form, the subform returns the[/color]
> > records[color=darkred]
> > > > > 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[/color][/color][/color]
list[color=blue][color=green]
> > box[color=darkred]
> > > > > (lstCollege), it returns values in the second list box[/color][/color][/color]
(lstAcadPlan)[color=blue][color=green][color=darkred]
> > > based
> > > > > on the first. The user then clicks on the cmdShowResults to[/color][/color][/color]
filter[color=blue][color=green]
> > and[color=darkred]
> > > > > return records in the datasheet subform. This works fine except[/color][/color][/color]
for[color=blue][color=green]
> > one[color=darkred]
> > > > > problem. Both list boxes are set up for single select values--I[/color][/color][/color]
now[color=blue][color=green][color=darkred]
> > > need
> > > > to
> > > > > make the second list box (lstAcadPlan) a multi-select list box and[/color]
> > pass[color=darkred]
> > > > the
> > > > > values to the filter. I have no idea how to include that in my[/color][/color][/color]
code[color=blue][color=green]
> > and[color=darkred]
> > > > was
> > > > > wondering if anyone had any ideas on what I should do. Here is[/color][/color][/color]
the[color=blue][color=green]
> > code[color=darkred]
> > > I
> > > > > have so far that works fine as long as only one value is selected[/color][/color][/color]
in[color=blue][color=green]
> > the[color=darkred]
> > > > > 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] = '" &[/color]
> > Me.lstAcadPlan[color=darkred]
> > > &
> > > > > "'"
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

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-carlsoro-SPAM@hotmail.com> wrote in message
news:%23%230dptiyDHA.1764@TK2MSFTNGP10.phx.gbl...[color=blue]
> When you say it does not filter, do you mean All records are returned or[/color]
No[color=blue]
> records are returned?
>
> Well, I see some obvious problems, none of which may be the major cause.
>
> 1) This section:[color=green]
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "[AcadPlan] IN ("[/color]
>
> should have an AND like so:
>[color=green]
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & " AND [AcadPlan] IN ("[/color]
>
> 2) This section:[color=green]
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID &[/color][/color]
"'"[color=blue][color=green]
> > Else
> > strWhere = "[EMPLID] = " & Me.txtEMPLID
> >
> > End If[/color]
> 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[/color]
a[color=blue]
> person with a Last name of O'Brien, this code will fail:[color=green]
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
> > Else
> > strWhere = "[Last] = '" & Me.txtLast & "'"
> > End If[/color]
>
> To fix, replace each apostrophe (') with TWO quotes ("")
>[color=green]
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [Last] = """ & Me.txtLast & """"
> > Else
> > strWhere = "[Last] = """ & Me.txtLast & """"
> > End If[/color]
>
> Now, as I said, these may not be the cause of the problem at hand. The[/color]
best[color=blue]
> 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[/color]
filter[color=blue]
> line. The code will stop before the filter executes and if you look in[/color]
the[color=blue]
> Debug (Immediate) window, you will see exactly what your Where clause[/color]
looks[color=blue]
> 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" <wwisnieski@admissions.umass.edu> wrote in message
> news:3fe894e4$1@news-1.oit.umass.edu...[color=green]
> > Thanks Roger. I used your code and it works great. I'm trying to[/color][/color]
modify[color=blue]
> it[color=green]
> > now for two reasons. Now, I have to add some more search criteria[/color][/color]
(State,[color=blue][color=green]
> > HonorsCollege, EMPLID, LastName) on the main form. Second, I need the[/color]
> user[color=green]
> > to be able to select from the college list without being required to[/color]
> select[color=green]
> > 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] = '" &[/color][/color]
Me.lstHonors[color=blue]
> &[color=green]
> > "'"
> > 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 &[/color][/color]
"'"[color=blue][color=green]
> > 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-carlsoro-SPAM@hotmail.com> wrote in message
> > news:ucPBGqMyDHA.2084@TK2MSFTNGP09.phx.gbl...[color=darkred]
> > > 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" <wwisnieski@admissions.umass.edu> wrote in message
> > > news:3fe7431e$1@news-1.oit.umass.edu...
> > > > Thanks Roger,
> > > >
> > > > I tried your code. I get a run time error and when I go to debug,[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > seems
> > > > to have combined the college and academic plan. For example,[/color][/color][/color]
strWhere[color=blue][color=green][color=darkred]
> > > > filter should show "[College] = CAS and [AcadPlan] = Art, Or[/color][/color]
> Philosophy,[color=green][color=darkred]
> > > Or
> > > > History". But it is showing [College]=CAS, Art, Philosophy,[/color][/color][/color]
History."[color=blue][color=green][color=darkred]
> > > >
> > > > William
> > > >
> > > >
> > > > "Roger Carlson" <NO-carlsoro-SPAM@hotmail.com> wrote in message
> > > > news:O7S%23fTKyDHA.3224@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[/color]
> > create[color=darkred]
> > > 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[/color][/color][/color]
like[color=blue][color=green][color=darkred]
> > > this:
> > > > >
> > > > > For i = 0 To lstAcadPlan.ListCount - 1
> > > > > If lstAcadPlan.Selected(i) Then
> > > > > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "',[/color][/color][/color]
"[color=blue][color=green][color=darkred]
> > > > > End If
> > > > > Next i
> > > > > strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
> > > > >
> > > > > This code loops through the values in the listbox. When it[/color][/color][/color]
reaches[color=blue][color=green]
> > one[color=darkred]
> > > > that
> > > > > is selected, it adds to the Where clause. When it reaches the[/color][/color][/color]
end,[color=blue]
> it[color=green][color=darkred]
> > > > lops
> > > > > the last comma off of the Where clause.
> > > > >
> > > > > (The actual syntax of the strWhere clause will vary depending on[/color]
> > details[color=darkred]
> > > > of
> > > > > your listbox control.)
> > > > >
> > > > > --
> > > > > --Roger Carlson
> > > > > www.rogersaccesslibrary.com
> > > > > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> > > > >
> > > > >
> > > > > "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in[/color][/color]
> message[color=green][color=darkred]
> > > > > news:3fe710b9$1@news-1.oit.umass.edu...
> > > > > > Hello Everyone,
> > > > > >
> > > > > > I have a main form with a datasheet subform that I use to query[/color][/color][/color]
by[color=blue][color=green][color=darkred]
> > > form.
> > > > > > After the user selects two criteria on the main form and clicks[/color][/color]
> the[color=green][color=darkred]
> > > > > > cmdShowResults button on the main form, the subform returns the
> > > records
> > > > > > based on the two criteria. The criteria used on the main form[/color][/color][/color]
are[color=blue][color=green][color=darkred]
> > > > values
> > > > > > selected in two list boxes. When the user clicks on the first[/color][/color]
> list[color=green][color=darkred]
> > > box
> > > > > > (lstCollege), it returns values in the second list box[/color][/color]
> (lstAcadPlan)[color=green][color=darkred]
> > > > based
> > > > > > on the first. The user then clicks on the cmdShowResults to[/color][/color]
> filter[color=green][color=darkred]
> > > and
> > > > > > return records in the datasheet subform. This works fine except[/color][/color]
> for[color=green][color=darkred]
> > > one
> > > > > > problem. Both list boxes are set up for single select values--I[/color][/color]
> now[color=green][color=darkred]
> > > > need
> > > > > to
> > > > > > make the second list box (lstAcadPlan) a multi-select list box[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > pass
> > > > > the
> > > > > > values to the filter. I have no idea how to include that in my[/color][/color]
> code[color=green][color=darkred]
> > > and
> > > > > was
> > > > > > wondering if anyone had any ideas on what I should do. Here is[/color][/color]
> the[color=green][color=darkred]
> > > code
> > > > I
> > > > > > have so far that works fine as long as only one value is[/color][/color][/color]
selected[color=blue]
> in[color=green][color=darkred]
> > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

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" <wwisnieski@admissions.umass.edu> wrote in message
news:3fe9b01b$1@news-1.oit.umass.edu...[color=blue]
> 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-carlsoro-SPAM@hotmail.com> wrote in message
> news:%23%230dptiyDHA.1764@TK2MSFTNGP10.phx.gbl...[color=green]
> > When you say it does not filter, do you mean All records are returned or[/color]
> No[color=green]
> > records are returned?
> >
> > Well, I see some obvious problems, none of which may be the major cause.
> >
> > 1) This section:[color=darkred]
> > > If Len(strWhere) > 0 Then
> > > strWhere = strWhere & "[AcadPlan] IN ("[/color]
> >
> > should have an AND like so:
> >[color=darkred]
> > > If Len(strWhere) > 0 Then
> > > strWhere = strWhere & " AND [AcadPlan] IN ("[/color]
> >
> > 2) This section:[color=darkred]
> > > If Len(strWhere) > 0 Then
> > > strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID &[/color][/color]
> "'"[color=green][color=darkred]
> > > Else
> > > strWhere = "[EMPLID] = " & Me.txtEMPLID
> > >
> > > End If[/color]
> > is using two different datatypes. If EMPLID is text, the first should[/color][/color]
be[color=blue][color=green]
> > 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[/color][/color]
have[color=blue]
> a[color=green]
> > person with a Last name of O'Brien, this code will fail:[color=darkred]
> > > If Len(strWhere) > 0 Then
> > > strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
> > > Else
> > > strWhere = "[Last] = '" & Me.txtLast & "'"
> > > End If[/color]
> >
> > To fix, replace each apostrophe (') with TWO quotes ("")
> >[color=darkred]
> > > If Len(strWhere) > 0 Then
> > > strWhere = strWhere & "And [Last] = """ & Me.txtLast &[/color][/color][/color]
""""[color=blue][color=green][color=darkred]
> > > Else
> > > strWhere = "[Last] = """ & Me.txtLast & """"
> > > End If[/color]
> >
> > Now, as I said, these may not be the cause of the problem at hand. The[/color]
> best[color=green]
> > 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[/color]
> filter[color=green]
> > line. The code will stop before the filter executes and if you look in[/color]
> the[color=green]
> > Debug (Immediate) window, you will see exactly what your Where clause[/color]
> looks[color=green]
> > 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" <wwisnieski@admissions.umass.edu> wrote in message
> > news:3fe894e4$1@news-1.oit.umass.edu...[color=darkred]
> > > Thanks Roger. I used your code and it works great. I'm trying to[/color][/color]
> modify[color=green]
> > it[color=darkred]
> > > now for two reasons. Now, I have to add some more search criteria[/color][/color]
> (State,[color=green][color=darkred]
> > > HonorsCollege, EMPLID, LastName) on the main form. Second, I need the[/color]
> > user[color=darkred]
> > > to be able to select from the college list without being required to[/color]
> > select[color=darkred]
> > > 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] = '" &[/color][/color]
> Me.lstHonors[color=green]
> > &[color=darkred]
> > > "'"
> > > 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 &[/color][/color][/color]
"'"[color=blue][color=green][color=darkred]
> > > 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 &[/color][/color]
> "'"[color=green][color=darkred]
> > > 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-carlsoro-SPAM@hotmail.com> wrote in message
> > > news:ucPBGqMyDHA.2084@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) & "',[/color][/color][/color]
"[color=blue][color=green][color=darkred]
> > > > 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" <wwisnieski@admissions.umass.edu> wrote in[/color][/color][/color]
message[color=blue][color=green][color=darkred]
> > > > news:3fe7431e$1@news-1.oit.umass.edu...
> > > > > Thanks Roger,
> > > > >
> > > > > I tried your code. I get a run time error and when I go to debug,[/color][/color]
> it[color=green][color=darkred]
> > > > seems
> > > > > to have combined the college and academic plan. For example,[/color][/color]
> strWhere[color=green][color=darkred]
> > > > > filter should show "[College] = CAS and [AcadPlan] = Art, Or[/color]
> > Philosophy,[color=darkred]
> > > > Or
> > > > > History". But it is showing [College]=CAS, Art, Philosophy,[/color][/color]
> History."[color=green][color=darkred]
> > > > >
> > > > > William
> > > > >
> > > > >
> > > > > "Roger Carlson" <NO-carlsoro-SPAM@hotmail.com> wrote in message
> > > > > news:O7S%23fTKyDHA.3224@tk2msftngp13.phx.gbl...
> > > > > > On my website (see sig below) is a small sample database called
> > > > > > "CreateQueries2.mdb". Form 6 in this database illustrates how[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > create
> > > > a
> > > > > > Where condition in code with a multi-select listbox. See the[/color][/color][/color]
code[color=blue][color=green][color=darkred]
> > > > behind
> > > > > > the form for details. In broad outline, it will work something[/color][/color]
> like[color=green][color=darkred]
> > > > this:
> > > > > >
> > > > > > For i = 0 To lstAcadPlan.ListCount - 1
> > > > > > If lstAcadPlan.Selected(i) Then
> > > > > > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) &[/color][/color][/color]
"',[color=blue]
> "[color=green][color=darkred]
> > > > > > End If
> > > > > > Next i
> > > > > > strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
> > > > > >
> > > > > > This code loops through the values in the listbox. When it[/color][/color]
> reaches[color=green][color=darkred]
> > > one
> > > > > that
> > > > > > is selected, it adds to the Where clause. When it reaches the[/color][/color]
> end,[color=green]
> > it[color=darkred]
> > > > > 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" <wwisnieski@admissions.umass.edu> wrote in[/color]
> > message[color=darkred]
> > > > > > news:3fe710b9$1@news-1.oit.umass.edu...
> > > > > > > Hello Everyone,
> > > > > > >
> > > > > > > I have a main form with a datasheet subform that I use to[/color][/color][/color]
query[color=blue]
> by[color=green][color=darkred]
> > > > form.
> > > > > > > After the user selects two criteria on the main form and[/color][/color][/color]
clicks[color=blue][color=green]
> > the[color=darkred]
> > > > > > > cmdShowResults button on the main form, the subform returns[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > records
> > > > > > > based on the two criteria. The criteria used on the main form[/color][/color]
> are[color=green][color=darkred]
> > > > > values
> > > > > > > selected in two list boxes. When the user clicks on the first[/color]
> > list[color=darkred]
> > > > box
> > > > > > > (lstCollege), it returns values in the second list box[/color]
> > (lstAcadPlan)[color=darkred]
> > > > > based
> > > > > > > on the first. The user then clicks on the cmdShowResults to[/color]
> > filter[color=darkred]
> > > > and
> > > > > > > return records in the datasheet subform. This works fine[/color][/color][/color]
except[color=blue][color=green]
> > for[color=darkred]
> > > > one
> > > > > > > problem. Both list boxes are set up for single select[/color][/color][/color]
values--I[color=blue][color=green]
> > now[color=darkred]
> > > > > need
> > > > > > to
> > > > > > > make the second list box (lstAcadPlan) a multi-select list box[/color][/color]
> and[color=green][color=darkred]
> > > > pass
> > > > > > the
> > > > > > > values to the filter. I have no idea how to include that in[/color][/color][/color]
my[color=blue][color=green]
> > code[color=darkred]
> > > > and
> > > > > > was
> > > > > > > wondering if anyone had any ideas on what I should do. Here[/color][/color][/color]
is[color=blue][color=green]
> > the[color=darkred]
> > > > code
> > > > > I
> > > > > > > have so far that works fine as long as only one value is[/color][/color]
> selected[color=green]
> > in[color=darkred]
> > > > 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 =[/color][/color][/color]
strWhere[color=blue][color=green][color=darkred]
> > > > > > > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn = True
> > > > > > > End Sub
> > > > > > >
> > > > > > > Thank you,
> > > > > > >
> > > > > > > William
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

Put the Debug line at the bottom of the procedure just above:[color=blue]
> Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter =[/color]
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" <wwisnieski@admissions.umass.edu> wrote in message
news:3fe9bda2$1@news-1.oit.umass.edu...[color=blue]
> 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 &[/color]
"'"[color=blue]
> and opened the immediate window. I select the first two criteria on the
> form and click the show results button and the line strWhere =[/color]
"[College][color=blue]
> = '" & 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" <wwisnieski@admissions.umass.edu> wrote in message
> news:3fe9b1b8$1@news-1.oit.umass.edu...[color=green]
> > 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" <wwisnieski@admissions.umass.edu> wrote in message
> > news:3fe9b01b$1@news-1.oit.umass.edu...[color=darkred]
> > > Roger....thanks again.
> > >
> > > Sorry for the confusion. Regarding the filtering problem, all records[/color][/color]
> are[color=green][color=darkred]
> > > being returned. Also, I don't quite understand the If Len(strWhere).[/color][/color]
> I'm[color=green][color=darkred]
> > > not sure which sections you are referring to. Should I be using that
> > > instead of say, If Len(Me.lstCollege & "") > 0[/color][/color][/color]
Then.....................[color=blue][color=green][color=darkred]
> > >
> > > William
> > >
> > >
> > > "Roger Carlson" <NO-carlsoro-SPAM@hotmail.com> wrote in message
> > > news:%23%230dptiyDHA.1764@TK2MSFTNGP10.phx.gbl...
> > > > When you say it does not filter, do you mean All records are[/color][/color][/color]
returned[color=blue]
> or[color=green][color=darkred]
> > > No
> > > > records are returned?
> > > >
> > > > Well, I see some obvious problems, none of which may be the major[/color][/color]
> cause.[color=green][color=darkred]
> > > >
> > > > 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] = '" &[/color][/color][/color]
Me.txtEMPLID[color=blue]
> &[color=green][color=darkred]
> > > "'"
> > > > > Else
> > > > > strWhere = "[EMPLID] = " & Me.txtEMPLID
> > > > >
> > > > > End If
> > > > is using two different datatypes. If EMPLID is text, the first[/color][/color][/color]
should[color=blue][color=green]
> > be[color=darkred]
> > > > 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[/color]
> > have[color=darkred]
> > > a
> > > > person with a Last name of O'Brien, this code will fail:
> > > > > If Len(strWhere) > 0 Then
> > > > > strWhere = strWhere & "And [Last] = '" & Me.txtLast &[/color][/color]
> "'"[color=green][color=darkred]
> > > > > 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 &[/color]
> > """"[color=darkred]
> > > > > Else
> > > > > strWhere = "[Last] = """ & Me.txtLast & """"
> > > > > End If
> > > >
> > > > Now, as I said, these may not be the cause of the problem at hand.[/color][/color]
> The[color=green][color=darkred]
> > > 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[/color][/color]
> in[color=green][color=darkred]
> > > the
> > > > Debug (Immediate) window, you will see exactly what your Where[/color][/color][/color]
clause[color=blue][color=green][color=darkred]
> > > 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" <wwisnieski@admissions.umass.edu> wrote in[/color][/color][/color]
message[color=blue][color=green][color=darkred]
> > > > news:3fe894e4$1@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[/color][/color]
> the[color=green][color=darkred]
> > > > user
> > > > > to be able to select from the college list without being required[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > > 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) & "',[/color][/color][/color]
"[color=blue][color=green][color=darkred]
> > > > > End If
> > > > > Next i
> > > > > strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
> > > > >
> > > > > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter =[/color][/color]
> strWhere[color=green][color=darkred]
> > > > > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn =[/color][/color][/color]
True[color=blue][color=green][color=darkred]
> > > > >
> > > > > ==================================
> > > > >
> > > > > Now here is what I have added. The good news is it doesn't return[/color][/color]
> any[color=green][color=darkred]
> > > > > 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) & "',[/color][/color][/color]
"[color=blue][color=green][color=darkred]
> > > > > 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[/color][/color][/color]
&[color=blue][color=green]
> > "'"[color=darkred]
> > > > > Else
> > > > > strWhere = "[State] = '" & Me.cboState & "'"
> > > > > End If
> > > > > End If
> > > > >
> > > > > If Len(Me.txtEMPLID & "") > 0 Then
> > > > > If Len(strWhere) > 0 Then
> > > > > strWhere = strWhere & "And [EMPLID] = '" &[/color][/color][/color]
Me.txtEMPLID[color=blue]
> &[color=green][color=darkred]
> > > "'"
> > > > > 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 &[/color][/color]
> "'"[color=green][color=darkred]
> > > > > Else
> > > > > strWhere = "[Last] = '" & Me.txtLast & "'"
> > > > > End If
> > > > > End If
> > > > >
> > > > > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lter =[/color][/color]
> strWhere[color=green][color=darkred]
> > > > > Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Fi lterOn =[/color][/color][/color]
True[color=blue][color=green][color=darkred]
> > > > >
> > > > > Thanks again for your help. It is much appreciated.
> > > > >
> > > > > William
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Roger Carlson" <NO-carlsoro-SPAM@hotmail.com> wrote in message
> > > > > news:ucPBGqMyDHA.2084@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) &[/color][/color]
> "',[color=green]
> > "[color=darkred]
> > > > > > 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" <wwisnieski@admissions.umass.edu> wrote in[/color]
> > message[color=darkred]
> > > > > > news:3fe7431e$1@news-1.oit.umass.edu...
> > > > > > > Thanks Roger,
> > > > > > >
> > > > > > > I tried your code. I get a run time error and when I go to[/color][/color]
> debug,[color=green][color=darkred]
> > > 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-carlsoro-SPAM@hotmail.com> wrote in[/color][/color][/color]
message[color=blue][color=green][color=darkred]
> > > > > > > news:O7S%23fTKyDHA.3224@tk2msftngp13.phx.gbl...
> > > > > > > > On my website (see sig below) is a small sample database[/color][/color]
> called[color=green][color=darkred]
> > > > > > > > "CreateQueries2.mdb". Form 6 in this database illustrates[/color][/color][/color]
how[color=blue][color=green]
> > to[color=darkred]
> > > > > create
> > > > > > a
> > > > > > > > Where condition in code with a multi-select listbox. See[/color][/color][/color]
the[color=blue][color=green]
> > code[color=darkred]
> > > > > > behind
> > > > > > > > the form for details. In broad outline, it will work[/color][/color]
> something[color=green][color=darkred]
> > > like
> > > > > > this:
> > > > > > > >
> > > > > > > > For i = 0 To lstAcadPlan.ListCount - 1
> > > > > > > > If lstAcadPlan.Selected(i) Then
> > > > > > > > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i)[/color][/color][/color]
&[color=blue][color=green]
> > "',[color=darkred]
> > > "
> > > > > > > > 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[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > end,
> > > > it
> > > > > > > lops
> > > > > > > > the last comma off of the Where clause.
> > > > > > > >
> > > > > > > > (The actual syntax of the strWhere clause will vary[/color][/color][/color]
depending[color=blue]
> on[color=green][color=darkred]
> > > > > details
> > > > > > > of
> > > > > > > > your listbox control.)
> > > > > > > >
> > > > > > > > --
> > > > > > > > --Roger Carlson
> > > > > > > > www.rogersaccesslibrary.com
> > > > > > > > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> > > > > > > >
> > > > > > > >
> > > > > > > > "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote[/color][/color][/color]
in[color=blue][color=green][color=darkred]
> > > > message
> > > > > > > > news:3fe710b9$1@news-1.oit.umass.edu...
> > > > > > > > > Hello Everyone,
> > > > > > > > >
> > > > > > > > > I have a main form with a datasheet subform that I use to[/color]
> > query[color=darkred]
> > > by
> > > > > > form.
> > > > > > > > > After the user selects two criteria on the main form and[/color]
> > clicks[color=darkred]
> > > > the
> > > > > > > > > cmdShowResults button on the main form, the subform[/color][/color][/color]
returns[color=blue][color=green]
> > the[color=darkred]
> > > > > > records
> > > > > > > > > based on the two criteria. The criteria used on the main[/color][/color]
> form[color=green][color=darkred]
> > > are
> > > > > > > values
> > > > > > > > > selected in two list boxes. When the user clicks on the[/color][/color]
> first[color=green][color=darkred]
> > > > list
> > > > > > box
> > > > > > > > > (lstCollege), it returns values in the second list box
> > > > (lstAcadPlan)
> > > > > > > based
> > > > > > > > > on the first. The user then clicks on the cmdShowResults[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > > filter
> > > > > > and
> > > > > > > > > return records in the datasheet subform. This works fine[/color]
> > except[color=darkred]
> > > > for
> > > > > > one
> > > > > > > > > problem. Both list boxes are set up for single select[/color]
> > values--I[color=darkred]
> > > > now
> > > > > > > need
> > > > > > > > to
> > > > > > > > > make the second list box (lstAcadPlan) a multi-select list[/color][/color]
> box[color=green][color=darkred]
> > > and
> > > > > > pass
> > > > > > > > the
> > > > > > > > > values to the filter. I have no idea how to include that[/color][/color][/color]
in[color=blue][color=green]
> > my[color=darkred]
> > > > code
> > > > > > and
> > > > > > > > was
> > > > > > > > > wondering if anyone had any ideas on what I should do.[/color][/color][/color]
Here[color=blue][color=green]
> > is[color=darkred]
> > > > 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 &[/color][/color]
> "'"[color=green][color=darkred]
> > > > > > > > > End If
> > > > > > > > > End If
> > > > > > > > > Forms(Me.Name)("sfrmSearchResults").Form.Filter =[/color]
> > strWhere[color=darkred]
> > > > > > > > > Forms(Me.Name)("sfrmSearchResults").Form.FilterOn =[/color][/color]
> True[color=green][color=darkred]
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > Thank you,
> > > > > > > > >
> > > > > > > > > William
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

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-carlsoro-SPAM@hotmail.com> wrote in message
news:%23%230dptiyDHA.1764@TK2MSFTNGP10.phx.gbl...[color=blue]
> When you say it does not filter, do you mean All records are returned or[/color]
No[color=blue]
> records are returned?
>
> Well, I see some obvious problems, none of which may be the major cause.
>
> 1) This section:[color=green]
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "[AcadPlan] IN ("[/color]
>
> should have an AND like so:
>[color=green]
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & " AND [AcadPlan] IN ("[/color]
>
> 2) This section:[color=green]
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [EMPLID] = '" & Me.txtEMPLID &[/color][/color]
"'"[color=blue][color=green]
> > Else
> > strWhere = "[EMPLID] = " & Me.txtEMPLID
> >
> > End If[/color]
> 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[/color]
a[color=blue]
> person with a Last name of O'Brien, this code will fail:[color=green]
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
> > Else
> > strWhere = "[Last] = '" & Me.txtLast & "'"
> > End If[/color]
>
> To fix, replace each apostrophe (') with TWO quotes ("")
>[color=green]
> > If Len(strWhere) > 0 Then
> > strWhere = strWhere & "And [Last] = """ & Me.txtLast & """"
> > Else
> > strWhere = "[Last] = """ & Me.txtLast & """"
> > End If[/color]
>
> Now, as I said, these may not be the cause of the problem at hand. The[/color]
best[color=blue]
> 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[/color]
filter[color=blue]
> line. The code will stop before the filter executes and if you look in[/color]
the[color=blue]
> Debug (Immediate) window, you will see exactly what your Where clause[/color]
looks[color=blue]
> 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" <wwisnieski@admissions.umass.edu> wrote in message
> news:3fe894e4$1@news-1.oit.umass.edu...[color=green]
> > Thanks Roger. I used your code and it works great. I'm trying to[/color][/color]
modify[color=blue]
> it[color=green]
> > now for two reasons. Now, I have to add some more search criteria[/color][/color]
(State,[color=blue][color=green]
> > HonorsCollege, EMPLID, LastName) on the main form. Second, I need the[/color]
> user[color=green]
> > to be able to select from the college list without being required to[/color]
> select[color=green]
> > 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] = '" &[/color][/color]
Me.lstHonors[color=blue]
> &[color=green]
> > "'"
> > 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 &[/color][/color]
"'"[color=blue][color=green]
> > 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-carlsoro-SPAM@hotmail.com> wrote in message
> > news:ucPBGqMyDHA.2084@TK2MSFTNGP09.phx.gbl...[color=darkred]
> > > 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" <wwisnieski@admissions.umass.edu> wrote in message
> > > news:3fe7431e$1@news-1.oit.umass.edu...
> > > > Thanks Roger,
> > > >
> > > > I tried your code. I get a run time error and when I go to debug,[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > seems
> > > > to have combined the college and academic plan. For example,[/color][/color][/color]
strWhere[color=blue][color=green][color=darkred]
> > > > filter should show "[College] = CAS and [AcadPlan] = Art, Or[/color][/color]
> Philosophy,[color=green][color=darkred]
> > > Or
> > > > History". But it is showing [College]=CAS, Art, Philosophy,[/color][/color][/color]
History."[color=blue][color=green][color=darkred]
> > > >
> > > > William
> > > >
> > > >
> > > > "Roger Carlson" <NO-carlsoro-SPAM@hotmail.com> wrote in message
> > > > news:O7S%23fTKyDHA.3224@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[/color]
> > create[color=darkred]
> > > 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[/color][/color][/color]
like[color=blue][color=green][color=darkred]
> > > this:
> > > > >
> > > > > For i = 0 To lstAcadPlan.ListCount - 1
> > > > > If lstAcadPlan.Selected(i) Then
> > > > > strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) & "',[/color][/color][/color]
"[color=blue][color=green][color=darkred]
> > > > > End If
> > > > > Next i
> > > > > strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
> > > > >
> > > > > This code loops through the values in the listbox. When it[/color][/color][/color]
reaches[color=blue][color=green]
> > one[color=darkred]
> > > > that
> > > > > is selected, it adds to the Where clause. When it reaches the[/color][/color][/color]
end,[color=blue]
> it[color=green][color=darkred]
> > > > lops
> > > > > the last comma off of the Where clause.
> > > > >
> > > > > (The actual syntax of the strWhere clause will vary depending on[/color]
> > details[color=darkred]
> > > > of
> > > > > your listbox control.)
> > > > >
> > > > > --
> > > > > --Roger Carlson
> > > > > www.rogersaccesslibrary.com
> > > > > Reply to: Roger dot Carlson at Spectrum-Health dot Org
> > > > >
> > > > >
> > > > > "William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in[/color][/color]
> message[color=green][color=darkred]
> > > > > news:3fe710b9$1@news-1.oit.umass.edu...
> > > > > > Hello Everyone,
> > > > > >
> > > > > > I have a main form with a datasheet subform that I use to query[/color][/color][/color]
by[color=blue][color=green][color=darkred]
> > > form.
> > > > > > After the user selects two criteria on the main form and clicks[/color][/color]
> the[color=green][color=darkred]
> > > > > > cmdShowResults button on the main form, the subform returns the
> > > records
> > > > > > based on the two criteria. The criteria used on the main form[/color][/color][/color]
are[color=blue][color=green][color=darkred]
> > > > values
> > > > > > selected in two list boxes. When the user clicks on the first[/color][/color]
> list[color=green][color=darkred]
> > > box
> > > > > > (lstCollege), it returns values in the second list box[/color][/color]
> (lstAcadPlan)[color=green][color=darkred]
> > > > based
> > > > > > on the first. The user then clicks on the cmdShowResults to[/color][/color]
> filter[color=green][color=darkred]
> > > and
> > > > > > return records in the datasheet subform. This works fine except[/color][/color]
> for[color=green][color=darkred]
> > > one
> > > > > > problem. Both list boxes are set up for single select values--I[/color][/color]
> now[color=green][color=darkred]
> > > > need
> > > > > to
> > > > > > make the second list box (lstAcadPlan) a multi-select list box[/color][/color][/color]
and[color=blue][color=green][color=darkred]
> > > pass
> > > > > the
> > > > > > values to the filter. I have no idea how to include that in my[/color][/color]
> code[color=green][color=darkred]
> > > and
> > > > > was
> > > > > > wondering if anyone had any ideas on what I should do. Here is[/color][/color]
> the[color=green][color=darkred]
> > > code
> > > > I
> > > > > > have so far that works fine as long as only one value is[/color][/color][/color]
selected[color=blue]
> in[color=green][color=darkred]
> > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

-----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:
[color=blue]
> 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
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

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

re: Code for Filtering from Mult-select List Box Criteria

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:Xk4Ib.25423$Pg1.13409@newsread1.news.pas.eart hlink.net...[color=blue]
> -----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:
>[color=green]
> > Hello Again,
> >
> > I finally was able to play around with the debug feature and can at[/color][/color]
least[color=blue][color=green]
> > 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[/color][/color]
(lstCollege[color=blue][color=green]
> > for example), I also get a run time error.
> >
> > When I use just the following code by itself, it works--as long I select[/color][/color]
one[color=blue][color=green]
> > 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[/color][/color]
State,[color=blue][color=green]
> > 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
> >[/color]
>[/color]

re: Code for Filtering from Mult-select List Box Criteria

-----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:[color=blue]
> 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:Xk4Ib.25423$Pg1.13409@newsread1.news.pas.eart hlink.net...
>[color=green]
>>-----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-----
>>
>>[/color][/color]
< snip previous posts >

re: Code for Filtering from Mult-select List Box Criteria

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" <PinkPanther2003@mail.com> wrote in message
news:ec70c0a1.0312292052.496c8ce3@posting.google.c om...[color=blue]
> 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[/color]

re: Code for Filtering from Mult-select List Box Criteria

"William Wisnieski" <wwisnieski@admissions.umass.edu> wrote in message news:<3ff3260c$1@news-1.oit.umass.edu>...[color=blue]
> 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()[/color]
<SNIP>[color=blue]
> 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[/color]

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...
[color=blue]
> For i = 0 To lstAcadPlan.ListCount - 1
> If lstAcadPlan.Selected(i) Then[/color]

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