By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,501 Members | 1,660 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,501 IT Pros & Developers. It's quick & easy.

how to filter report based on the list you selected from listbox in a form?

100+
P: 180
I want to filter report to show only field you selected from a listbox in a form.
For example I have query which field are ID, Ni, Fe, Mg and Sc
The field ID is primary key. it has value of 1 meaning first record. then the value of field Ni is 50, Fe is 49, Mg is 1.124 and Sc is 3.014. I have a listbox in a form which name is listfilter, then it's row source type is field list which shows;
ID
Ni
Fe
Mg
Sc
then I have command button in form that when you click, it shows report. If I didn't select anything in listbox then the report must show none, but when I select Ni, it must show only Ni.
How can I do this?
Apr 16 '16 #1

✓ answered by PhilOfWalton

I'm going to make some assumptions as the information given is a little sparse.
I assume you have a table of Elements something like
ElememntID
ElememntName
ElementSymbol

I am assuming your list box is bound to
SELECT Elements.ElementSymbol, Elements.ElementName FROM Elements ORDER BY Elements.ElementSymbol;
and the bound column is 1 and it is a Simple MultiSelect box

In this example the form is Form3 and the List Box is List3 and the report is RptElements

Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintIt_Click()
  2.  
  3.     Dim stDocCriteria As String
  4.     Dim VarItem As Variant
  5.  
  6.     For Each VarItem In Forms!Form3!List3.ItemsSelected
  7.         stDocCriteria = stDocCriteria & "ElementSymbol = " & Chr$(34) & Forms!Form3!List3.ItemData(VarItem) & Chr$(34) & " OR "
  8.     Next VarItem
  9.     If stDocCriteria <> "" Then
  10.         stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
  11.     Else
  12.         stDocCriteria = "True"
  13.     End If
  14.  
  15.     DoCmd.OpenReport "RptElements", acViewPreview, , stDocCriteria
  16.  
  17. End Sub
  18.  
Hope that helps

Phil

Share this Question
Share on Google+
6 Replies


Seth Schrock
Expert 2.5K+
P: 2,937
So, just to clarify, are you wanting to filter rows or fields? I think that you are meaning filtering fields. If I'm correct, is it possible to select multiple fields in the listbox? This would change how it would be done.

If you only want a report to show if something is selected in your listbox, then you need to add that check to your button's OnClick event.
Apr 16 '16 #2

100+
P: 180
if possible to select multiple fields in a listbox so that based on your selected fields in listbox, thats only the fields that report will show. or If it can be done by checkbox then how to do it?
Apr 16 '16 #3

PhilOfWalton
Expert 100+
P: 1,430
I'm going to make some assumptions as the information given is a little sparse.
I assume you have a table of Elements something like
ElememntID
ElememntName
ElementSymbol

I am assuming your list box is bound to
SELECT Elements.ElementSymbol, Elements.ElementName FROM Elements ORDER BY Elements.ElementSymbol;
and the bound column is 1 and it is a Simple MultiSelect box

In this example the form is Form3 and the List Box is List3 and the report is RptElements

Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintIt_Click()
  2.  
  3.     Dim stDocCriteria As String
  4.     Dim VarItem As Variant
  5.  
  6.     For Each VarItem In Forms!Form3!List3.ItemsSelected
  7.         stDocCriteria = stDocCriteria & "ElementSymbol = " & Chr$(34) & Forms!Form3!List3.ItemData(VarItem) & Chr$(34) & " OR "
  8.     Next VarItem
  9.     If stDocCriteria <> "" Then
  10.         stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
  11.     Else
  12.         stDocCriteria = "True"
  13.     End If
  14.  
  15.     DoCmd.OpenReport "RptElements", acViewPreview, , stDocCriteria
  16.  
  17. End Sub
  18.  
Hope that helps

Phil
Apr 16 '16 #4

100+
P: 180
thank u philhalton, that really helps. I just need to modify it more. thanks.
Apr 17 '16 #5

Seth Schrock
Expert 2.5K+
P: 2,937
The solution that Phil gave filters records, not fields. Is this what you are looking for? Based on post #3, I thought that you were trying to only show certain fields, not certain records that had something in certain fields.
Apr 17 '16 #6

NeoPa
Expert Mod 15k+
P: 31,398
Eneyardi has had his account suspended for two weeks due to persistent failure to post in a reasonable manner. They are very careless of details and others' time.

This thread, which was also double-posted, is a good example of how much time and efforts are wasted trying to deal with such carelessness.

NB. This isn't a matter of a foreign user struggling, as indicated clearly by his failure even to get PhilOfWalton's name right when responding.
Apr 18 '16 #7

Post your reply

Sign in to post your reply or Sign up for a free account.