Code for Filtering from Mult-select List Box Criteria

W

William Wisnieski

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
 
R

Roger Carlson

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.)
 
W

William Wisnieski

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 said:
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 said:
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
Click to expand...
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
Click to expand...
Click to expand...
 
R

Roger Carlson

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 said:
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 said:
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


need pass
the and
was
Click to expand...
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] = '" &
Click to expand...
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
S

Salad

William said:
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:
Click to expand...

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
 
W

William Wisnieski

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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = 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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = True

Thanks again for your help. It is much appreciated.

William







Roger Carlson said:
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 said:
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 said:
On my website (see sig below) is a small sample database called
"CreateQueries2.mdb". Form 6 in this database illustrates how to
Click to expand...
Click to expand...
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
Click to expand...
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
Click to expand...
details
of
your listbox control.)

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


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
Click to expand...
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] = '" &
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
R

Roger Carlson

When you say it does not filter, do you mean All records are returned or No
records are returned?

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

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

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

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

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

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

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

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 said:
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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = 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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = True

Thanks again for your help. It is much appreciated.

William







Roger Carlson said:
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 said:
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
Click to expand...
Philosophy,
Or
History". But it is showing [College]=CAS, Art, Philosophy, History."

William


On my website (see sig below) is a small sample database called
"CreateQueries2.mdb". Form 6 in this database illustrates how to
Click to expand...
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


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
Click to expand...
list
box
(lstCollege), it returns values in the second list box (lstAcadPlan)
based
on the first. The user then clicks on the cmdShowResults to
Click to expand...
filter
and
return records in the datasheet subform. This works fine except
Click to expand...
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
Click to expand...
code
and
was
wondering if anyone had any ideas on what I should do. Here is
Click to expand...
the
code
I
have so far that works fine as long as only one value is selected
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
W

William Wisnieski

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 said:
When you say it does not filter, do you mean All records are returned or No
records are returned?

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

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

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

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

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

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

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

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 said:
Thanks Roger. I used your code and it works great. I'm trying to
Click to expand...
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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = 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] = '" &
Click to expand...
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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = True

Thanks again for your help. It is much appreciated.

William







Roger Carlson said:
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

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


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
Click to expand...
reaches
one
that
is selected, it adds to the Where clause. When it reaches the
Click to expand...
Click to expand...
end,
it
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
W

William Wisnieski

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 said:
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 said:
When you say it does not filter, do you mean All records are returned or No
records are returned?

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

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

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

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

End If
Click to expand...
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
Click to expand...
have
a
person with a Last name of O'Brien, this code will fail:
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Last] = '" & Me.txtLast & "'"
Else
strWhere = "[Last] = '" & Me.txtLast & "'"
End If
Click to expand...

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

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 said:
Thanks Roger. I used your code and it works great. I'm trying to
Click to expand...
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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = 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] = '" &
Click to expand...
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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = True

Thanks again for your help. It is much appreciated.

William







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

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


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) &
Click to expand...
Click to expand...
"',
" end,
Click to expand...
query
by
form.
After the user selects two criteria on the main form and
Click to expand...
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
Click to expand...
except
for
one
problem. Both list boxes are set up for single select
Click to expand...
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
Click to expand...
my
code
and
was
wondering if anyone had any ideas on what I should do. Here
Click to expand...
is
the
code
I
have so far that works fine as long as only one value is
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
W

William Wisnieski

Roger,

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

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

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

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

William








William Wisnieski said:
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 said:
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 said:
When you say it does not filter, do you mean All records are returned
Click to expand...
or
No
records are returned?

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

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

should have an AND like so:

If Len(strWhere) > 0 Then
strWhere = strWhere & " AND [AcadPlan] IN ("

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

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

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

To fix, replace each apostrophe (') with TWO quotes ("")

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

Now, as I said, these may not be the cause of the problem at hand.
Click to expand...
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
Click to expand...
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

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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = 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
Click to expand...
&
"'"
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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = True

Thanks again for your help. It is much appreciated.

William







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) &
Click to expand...
Click to expand...
"',
"
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

Thanks Roger,

I tried your code. I get a run time error and when I go to
Click to expand...
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


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
Click to expand...
something
like
this:

For i = 0 To lstAcadPlan.ListCount - 1
If lstAcadPlan.Selected(i) Then
strWhere = strWhere & "'" & lstAcadPlan.Column(0, i) &
Click to expand...
"',
"
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


message
Hello Everyone,

I have a main form with a datasheet subform that I use to
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
R

Roger Carlson

Put the Debug line at the bottom of the procedure just above:
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.Filter =
Click to expand...
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 said:
Roger,

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

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

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

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

William








William Wisnieski said:
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
Click to expand...
returned
or
No
records are returned?

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

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

should have an AND like so:

If Len(strWhere) > 0 Then
strWhere = strWhere & " AND [AcadPlan] IN ("

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

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

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

To fix, replace each apostrophe (') with TWO quotes ("")

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

Now, as I said, these may not be the cause of the problem at hand. The
best
thing to do in a situation like this is to put the following line:

Debug.Print strWhere

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

HTH

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

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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = 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
Click to expand...
&
"'"
Else
strWhere = "[State] = '" & Me.cboState & "'"
End If
End If

If Len(Me.txtEMPLID & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [EMPLID] = '" &
Click to expand...
Click to expand...
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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = True

Thanks again for your help. It is much appreciated.

William







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) &
Click to expand...
"',
"
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

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


On my website (see sig below) is a small sample database called
"CreateQueries2.mdb". Form 6 in this database illustrates
Click to expand...
how
to
create
a
Where condition in code with a multi-select listbox. See
Click to expand...
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)
Click to expand...
&
"',
"
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
Click to expand...
Click to expand...
depending
on
details
of
your listbox control.)

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


message
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
Click to expand...
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
Click to expand...
in
my
code
and
was
wondering if anyone had any ideas on what I should do.
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
W

William Wisnieski

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 said:
When you say it does not filter, do you mean All records are returned or No
records are returned?

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

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

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

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

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

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

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

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 said:
Thanks Roger. I used your code and it works great. I'm trying to
Click to expand...
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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = 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] = '" &
Click to expand...
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.Filter = strWhere
Forms(Me.Name)("sfrmRoundUpSearchResults").Form.FilterOn = True

Thanks again for your help. It is much appreciated.

William







Roger Carlson said:
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

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


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
Click to expand...
reaches
one
that
is selected, it adds to the Where clause. When it reaches the
Click to expand...
Click to expand...
end,
it
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
M

MGFoster

-----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/DRu4echKqOuFEgEQK3vQCfTTmCDIhyH1TvV4ITHjDX9M4WdawAn3f2
QZR+Xw0zx0fkgGb3ikptpoSA
=S6v1
-----END PGP SIGNATURE-----


William said:
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
Click to expand...
 
P

Pink Panther

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
 
W

William Wisnieski

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 said:
-----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/DRu4echKqOuFEgEQK3vQCfTTmCDIhyH1TvV4ITHjDX9M4WdawAn3f2
QZR+Xw0zx0fkgGb3ikptpoSA
=S6v1
-----END PGP SIGNATURE-----


William said:
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
Click to expand...
Click to expand...
 
M

MGFoster

-----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+vUXseRVQAoLAO
VwMh3FCvvLN9HDc5uI6guZP0
=i+dA
-----END PGP SIGNATURE-----


William said:
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




-----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/DRu4echKqOuFEgEQK3vQCfTTmCDIhyH1TvV4ITHjDX9M4WdawAn3f2
QZR+Xw0zx0fkgGb3ikptpoSA
=S6v1
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
< snip previous posts >
 
W

William Wisnieski

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.Recordset
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 said:
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
Click to expand...
 
P

Pieter Linden

William Wisnieski said:
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()
Click to expand...
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
Click to expand...

Well, first off, well done on getting it to work. Now that it works,
it might be time for some tweaking...

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run select query from VBA code where criteria comes from a multi select ListBox 2
mutli-select list box problem 2
MultiList Select Box Search Form 7
Multiselect List Box 4
Dependant Multi-Selection List Boxes~HELP, I'm Stuck! 1
Change record source button 3
Null Values as Option in List Box 5
Multi Select List Box 4

Top