 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / February 2006multiple selection filter
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, >I connected sub and main forms by child/parent link using 'year'. >The filter works fine as long as the selection is not multiple. [quoted text clipped - 5 lines] >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] 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 >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] 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. 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, > >Here is the whole code. > >Hope to hear some suggestions very soon! [quoted text clipped - 6 lines] > > John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.