Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-19
6
Medium Priority
?
812 Views
Last Modified: 2015-01-21
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!
0
Comment
Question by:Megin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40558758
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
 

Author Comment

by:Megin
ID: 40558867
Thank you! I am going to start working on this tomorrow.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 500 total points
ID: 40558954
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 40561026
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
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 500 total points
ID: 40562724
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
 

Author Closing Comment

by:Megin
ID: 40563330
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question