List Box and Query

Z

ZIMMJE

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
 
D

Douglas J. Steele

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
 
Z

ZIMMJE

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

Douglas J. Steele said:
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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

Douglas J. Steele

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


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


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

Douglas J. Steele said:
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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

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

report based on selected items in list box(multiselect) 0
print previewing rpt based on selected items in list box(mulitsele 0
Syntax Error (missing operator) in Query Expresison 5
Moving Items in a Multicolumn List Box 0
Synchronized Combo - Boxes Issues 4
pick box based on pick box sorted - how do i store the contents of unbound contr 0
Loop Until Problem 2
list or combo box 0

Top