Solved

Filter report with drop down box

Posted on 2013-10-25
2
820 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 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