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

I want to filter a report using a field, but add an option to not filter too.

P: 3
I'm very new to Access and found no details online to help solve my problem - outlined below.

I have a simple D/B that holds a booklist (small library). There is a category field that determines the book category (history, geography, etc). I wish to run a report based on this field and created a filter to do this, which works. However, I also want to use the same report unfiltered which I'm struggling with. I know I can create a separate unfiltered report but that seems like redundancy.

I followed an online tutorial to set this up. I created a simple query for the report. I created a simple form with a combo and button. The combo is linked to the category field. The button calls up the report. The query lists all the fields I want in the report, along with the category field. I included a Criteria in the query using the expression builder for the category field, setting it to the combo in the form [Forms]![RunReportFORM]![Category].
This design means the form must always call the report and the report will only list the records of the specified category.
I tried to call the report without loading the form and a message dialog appears asking for the combo. That makes sense.
I think I have 2 theoretical options, though I'm sure there are better ones.
1) Change the criteria to detect if the form is open and if it's not, then set the criteria to the Category field. I tried to do this but failed to find the correct code - Used IIf("some way to find if the form is open", [Forms]![RunReportFORM]![Category], [Category])
2) As well as the list of Categories, I could add a "All Categories" option in the Combo, but I can't find a way to do this - It may well be easy but I've little MSAccess experience.

Any help you could offer would be gratefully appreciated.

Regards,
Tim
Aug 2 '17 #1

✓ answered by PhilOfWalton

Thanks Tim.

I would suggest you have another table for Authors. With 4000 books, I doubt that you have 4000 authors, and the advantage is that it is very simply to build a form to show all the books from a particular Author.

Note that I have changed the names of some of the tables and fields so that names tally up better.

I had a very simple Db for books and have made a few Mods to show you a few useful techniques.

Note in particular (Your Problem) the RowSource of the ComboBox CboFindCategory. This uses a Union Query which is a little advanced, but can be very useful.

Phil

Share this Question
Share on Google+
4 Replies


PhilOfWalton
Expert 100+
P: 1,430
Tim, it's comparatively easy to do, but as you are a beginner, it is probably worth checking that your database is normalised.

So could you send an image of your relationship pane with all the tables showing all the fields

Phil
Aug 2 '17 #2

P: 3
Hi Phil,
The database is the simplest I've ever seen (It's not mine - I was asked to help fix this), that's why it's so frustrating not being able to resolve this. It's so simple (and small - 4000 to 5000 records) that I don't think further normalisation is required or possible. I could be wrong - Feel free to correct me.

... and thanks for responding so quickly.

Hopefully this image attaches...

Tim
Attached Images
File Type: jpg xxx.jpg (18.0 KB, 52 views)
Aug 2 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Thanks Tim.

I would suggest you have another table for Authors. With 4000 books, I doubt that you have 4000 authors, and the advantage is that it is very simply to build a form to show all the books from a particular Author.

Note that I have changed the names of some of the tables and fields so that names tally up better.

I had a very simple Db for books and have made a few Mods to show you a few useful techniques.

Note in particular (Your Problem) the RowSource of the ComboBox CboFindCategory. This uses a Union Query which is a little advanced, but can be very useful.

Phil
Aug 2 '17 #4

P: 3
Thank you Phil. Good solution.
Aug 4 '17 #5

Post your reply

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