multiple selection filter

[Guest]

I am having difficulty filtering form/report using multiple selection via
list box.
I put a subform in a report that shows a chart based on dataset selected by
users.
I connected sub and main forms by child/parent link using 'year'.
The filter works fine as long as the selection is not multiple.
But it fails when a user chooses more than one selections from the list box.
For example, when a user selects year 2004 and 2005, the chart only shows
records from 2004, and not an aggregation of 2004 and 2005.

The filter string that is passed to the main form looks fine, the base query
used for subform also produces what I want when I try the filter string
manually.

Any suggestions of the possible cause of this problems?
Thanks a lot in advance,

 

[John Vinson]

I connected sub and main forms by child/parent link using 'year'.
The filter works fine as long as the selection is not multiple.
But it fails when a user chooses more than one selections from the list box.
For example, when a user selects year 2004 and 2005, the chart only shows
records from 2004, and not an aggregation of 2004 and 2005.

The filter string that is passed to the main form looks fine, the base query
used for subform also produces what I want when I try the filter string
manually.

Please post your code and the filter string.

John W. Vinson[MVP]

 

[Guest]

Thank you for your reply, John.
Here is the whole code.
Hope to hear some suggestions very soon!


Private Sub CmdOpenReport_Click()

DoCmd.OpenReport "Country Pie", acViewPreview
'store value of CmbProduct to str
If IsNull(Me.CmbProduct.Value) Then
StrProduct = ""
Else
StrProduct = "[_product]= '" & Me.CmbProduct.Value & "'"
End If

'store value of LstReportingYear to str
StrReportingYear = ""
For Each VarItem In Me!LstReportingYear.ItemsSelected
StrReportingYear = StrReportingYear & ", " &
Me!LstReportingYear.ItemData(VarItem) & ""
Next VarItem

'tip off unnecessary char from StrReportingYear
StrReportingYear = Right(StrReportingYear, Len(StrReportingYear) - 2)
StrReportingYear = "[_reporting_year] IN (" & StrReportingYear & ")"


If StrProduct = "" Then
MsgBox "Please choose a product."
ElseIf StrReportingYear = "" Then
MsgBox "Please choose reporting year(s)."
Else

'create StrFilter
StrFilter = StrProduct & " AND " & StrReportingYear
Debug.Print StrFilter

'Filter report
With Reports![Country Pie]
.Filter = StrFilter
.FilterOn = True
End With

End If

End Sub

 

[John Vinson]

Here is the whole code.
Hope to hear some suggestions very soon!

What is the datatype of _Reporting_Year? (and why the prefix
underscore?)

Could you post the actual generated strFilter for a criterion which
does not work, and indicate which records are and are not reported?

John W. Vinson[MVP]

 

[Guest]

Hi John,

The data format for _reporting_year is number.
I am importing the data from a different DB, updating data once in a while
manually, and to make my life easier, I keep the column name and data type as
the same as the original DB.
So that is the reason why _reporting_year is not in date format, and why it
has prefix underscore.
I have encountered the same problem with other column both with numbers and
texts.

The filter string looks like the following.

[_product]= 'Banana' AND [_reporting_year] IN (2004, 2005)

I hope I will be able to hear some advices this time.

Thank you a lot.

 

[Guest]

Sorry I forgot to mention which data is and is not selected.
For the filter
[_product]= 'Banana' AND [_reporting_year] IN (2004, 2005)

I get the result that is the same one as I command
[_product]= 'Banana' AND [_reporting_year] IN (2004)

and the data does not reflect records
[_product]= 'Banana' AND [_reporting_year] IN (2005)

When I apply the same condition to the based query, I get the correct result
that reflect both 2004 and 2005 records.

Thanks for your help,