Which is better? Using a multiselect list box to filter a query or to filter a report?

I am setting up a form in my Access database that has several multi select list boxes that are going to be used to set the criteria for a query that will serve as the basis for a report.

I have been scanning the internet trying to find the best way to do this and now I have a question for you all.

What is better? Using the list boxes to filter the query or using them to set the criteria for the report? Is there much of a difference?

I am pretty fuzzy on how to do either, but would like to see some opinions about which would be better.

Some factors may include: The query has many tables and there are multiple multiselect list boxes.

All advice is welcome!
MeginAsked:
Who is Participating?
 
Helen FeddemaConnect With a Mentor Commented:
My Fancy Filters sample database illustrates using criteria of various types to filter a subform or report:
http://www.helenfeddema.com/Files/accarch129.zip
Fancy Filters form
and this one shows how to filter by multiple selections in a multi-select listbox:
http://www.helenfeddema.com/Files/accarch197.zip
Multi-select listbox filtering
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Generally speaking, I think you're better off sending criteria to the report via the OpenReport method. This normally allows you to design more "generic" forms and filter them it as needed.

To use this method, you just create a standard WHERE clause without the word "WHERE", and send that as the WhereClause argument of the OpenReport method:

Dim s As String
Dim var As Variant

For each var in Me.YourListBox.ItemsSelected
  s = s & Me.YourListBox.ItemsSelected(var, 0) & ","
Next

s = Left(s, Len(s)-1)

DoCmd.OpenReport "MyReport", acViewPreview, , "SomeField IN (" & s & ")"

http://msdn.microsoft.com/en-us/library/office/ff192676%28v=office.15%29.aspx

And, if you're using MultiSelect listboxes, you're pretty well stuck with the OpenReport method.
0
 
MeginAuthor Commented:
Thank you! I am going to start working on this tomorrow.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Nick67Connect With a Mentor Commented:
Using the list boxes to filter the query or using them to set the criteria for the report? Is there much of a difference?
There's, by definition, going to be absolutely no difference in the end result.  After all, you want to create a report that has a certain set of data in it.

There's three ways of going about it, and all three of them are useful to have in the toolbox going forward.
A 'parameter and launching' form that has controls on it that supply critical information to a report and command buttons (or perhaps comboboxes using the AfterUpdate event) is a very good thing to develop.paramter form
The WhereClause argument of OpenReport as @Scott mentioned is very useful when you have a general report that contains many, many records and it is useful to generate subsets of those records in smaller, more detailed subsets.

The third is the last optional argument of OpenReports.  That's OpenArgs.  It's a string of great possible length--and it can be anything you can compose.  I like passing in a string of arguments delimited by semi-colons  "PrintPaperYes;EmailPDFYes;Select * from TempTable where ID = 40;BadID = 75"  You then put OpenArgs through a Split() in the Report_Open Event and do whatever you'd like with the results
Dim MyArgs() as string
MyArgs = Split(Nz(me.OpenArgs,""),";")
If MyArgs(0) = "PrintPaperYes" then ....
If MyArgs(1) = "EmailPDFYes" then ....
Me.recordsource = MyArgs(2)
Me.Filter = MyArgs(3)
me.FilterOn =True
 
These possibilities are pretty much endless.

All three are very highly useful techniques to be able to work with.
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
As with anything, there is no "Better" way to do anything, ...different approaches work for varying circumstances.

I, personally, would filter the report.
If you filter the query, ...and the query gets deleted by accident, ...you loose your filter.

Other than that, pick the way that you are more comfortable with...
;-)

JeffCoachman
0
 
MeginAuthor Commented:
Thank you, everyone, for the suggestions! I think I can move forward with this now. I just wanted to know what everyone thought and you all gave me more than enough to work with.

Again, Thank you!!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.