List Box and Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ZIMMJE - 08 Mar 2007 18:57 GMT

I have a list box that you can select a group name from. When you click
print preview I then have a Graph (that is fed by
qryGroupSummary_AreaSummary) that should display only the group that was
selected from the list. Below is what I have so far. In Debug everything
looks correct except the where statement. the strRptFilter contaions the
field I select but I am not sure how to have that selection select the
correct records in the qry that feeds my graph.

Please help I am sure I am making this to difficult.

Private Sub Command7_Click()

Dim strRptFilter As String
Dim lngLoop As Long
strRptFilter = ""
For lngLoop = 0 - [PickList].ColumnHeads To [PickList].ListCount - 1
If [PickList].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & _
[PickList].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "chtHSA_Group", acViewPreview, ,
[qryGroupSummary_AreaAummary].[GROUPS] = strRptFilter

Reply to this Message

Douglas J. Steele - 08 Mar 2007 19:16 GMT

The filter needs to be something like [GROUPS] = 1 Or [GROUPS] = 3 Or
[GROUPS] = 4. Alternatively, it could be [GROUPS] IN (1, 3, 4). Your code is
only capable of giving [GROUPS] = 1 Or 3 Or 4, which will not work. (Note
that since field names cannot be duplicated in a recordsource, you do not
use the query or table name as a qualifier)

Try:

Private Sub Command7_Click()

Dim strRptFilter As String
Dim lngLoop As Long
strRptFilter = ""
For lngLoop = 0 - [PickList].ColumnHeads To [PickList].ListCount - 1
If [PickList].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & _
[PickList].ItemData(lngLoop) & ", "
Next lngLoop
If Len(strRptFilter) > 4 Then
strRptFilter = Left(strRptFilter, Len(strRptFilter) - 2)
strRptFilter = "[GROUPS] IN (" & strRptFilter & ")"
End If
DoCmd.OpenReport "chtHSA_Group", acViewPreview, , strRptFilter

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I have a list box that you can select a group name from. When you click
> print preview I then have a Graph (that is fed by
[quoted text clipped - 20 lines]
> DoCmd.OpenReport "chtHSA_Group", acViewPreview, ,
> [qryGroupSummary_AreaAummary].[GROUPS] = strRptFilter

Reply to this Message

ZIMMJE - 08 Mar 2007 20:04 GMT

Thank you for the advice. I now get the following error msg:
Syntax error (missing operator) in query expression
'([GROUPS] IN (123 ABC Health Group - Cardiology))'

Any additional suggestions

JZ

> The filter needs to be something like [GROUPS] = 1 Or [GROUPS] = 3 Or
> [GROUPS] = 4. Alternatively, it could be [GROUPS] IN (1, 3, 4). Your code is
[quoted text clipped - 44 lines]
> > DoCmd.OpenReport "chtHSA_Group", acViewPreview, ,
> > [qryGroupSummary_AreaAummary].[GROUPS] = strRptFilter

Reply to this Message

Douglas J. Steele - 08 Mar 2007 22:16 GMT

You didn't mention it was a text field.

For lngLoop = 0 - [PickList].ColumnHeads To [PickList].ListCount - 1
If [PickList].Selected(lngLoop) = True Then
strRptFilter = strRptFilter & Chr$(34) & _
[PickList].ItemData(lngLoop) & Chr$(34) & ", "
End If
Next lngLoop

Actually, a little more efficient would be:

Dim varEntry As Variant

For Each varEntry In [PickList].ItemsSelected
strRptFilter = strRptFilter & Chr$(34) & _
[PickList].ItemData(varEntry) & Chr$(34) & ", "
Next varEntry

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thank you for the advice. I now get the following error msg:
> Syntax error (missing operator) in query expression
[quoted text clipped - 56 lines]
>> > DoCmd.OpenReport "chtHSA_Group", acViewPreview, ,
>> > [qryGroupSummary_AreaAummary].[GROUPS] = strRptFilter

Reply to this Message