Solved

Filter report with drop down box

Posted on 2013-10-25
2
732 Views
Last Modified: 2013-10-28
I have a report "copy or trader holdings" that uses the query "copy or trader holdings" as its data source. I'd like to be able to filter the "books" shown in the report from the current all books to a single book. The list of books is in a table named "books," and I would like to be able to select from a drop down list that contains two fields: "trader" and "description," with the "trader" number being used to display only that "book" in the report.
0
Comment
Question by:jtbold2
2 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39601847
You could create a form with a combo based on you "Books" table. I'm not sure exactly how that table is related to your "copy or trader holdings" query, so it's hard to say, but generally could do something like this on that form (perhaps in a button click event):

Dim s As String

If Nz(Me.YourBooksCombo.Column(0) , "") <> "" Then
  '/ the user selected something
  s = "YourBookFieldInTheQuery=" & Me.YourBooksCombo.Column(0)
End If

DoCmd.OpenReport "Copy or Trader Holdings", , , s

Obviously you'd have to change the names of the Forms and Report, as well as make sure that your Fields "match up" - that is, you replace "YourBookFieldInTheQuery" with the actual field name, and so on.
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 250 total points
ID: 39602753
Take a look at my Fancy Filters sample database.  It lets you filter by one or more fields of various data types, displaying the results in a datasheet subform, or print them in a report.
Here is a link for downloading it:

http://www.helenfeddema.com/Files/accarch129.zip

and here is a screen shot of the form:

Fancy Filters form
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now