Want to add Filter using 2 fields as the filter

[eepok]

Me again.

*Disclaimer* I don't know the first thing about VBA or any other program language and am attempting to create a simple solution for a departmental problem.


Ok, so I got a book order database set up now. The only weakness in it is that each quarter the records have to be dumped (and re-populated with current data) if the user wants to batch print all these letters to publishers and another report for our university book store.

So what I would like to do is filter the orders in this database by 2 fields: [Academic Quarter] and [Year]. What I am imagining is something simple like two list boxes in the form header and a button to press. One would choose a quarter and a year, press the button, and a report displaying all the filtered records would pop up.

Is this possible without coding -- are there built in functions in Access 2003 to help me do this?

 

[Zigzag]

Hi eepok,

I don’t think that I fully understand what you are looking for, so I have a few questions,

When you say that each quarter you have to "dump (and re-populated with current data)" what are you "repopulating" this data with or is this data just having certain fields changed i.e. the date. If this is the case then search for "Update Query".

So what I would like to do is filter the orders in this database by 2 fields: [Academic Quarter] and [Year]. What I am imagining is something simple like two list boxes in the form header and a button to press. One would choose a quarter and a year, press the button, and a report displaying all the filtered records would pop up

This can be done in several different ways.
The method that I prefer is to create an unbound form with two combo boxes’ or Text box's on, within this unbound form add a subform. The subform data source would be a query base on the table where [Academic Quarter] & [Year] are come from and then queried like the main forms combo Box's

Typical code

Like ([Forms]![Suppliers]![Supplier])

When the combo box's are changed this would then cause an event procedure to fire. "Me.refresh" this would basically be the only VBA code you would need.

Hope this has helped.

Garry

 

[eepok]

To clarify:

The functions of my database:
Ease data entry complications (they used to use a spreadsheet with 42 columns)
Easy creation of order request of text books for bookstore
Easy creation of multiple forms of requests for desk copies of text books from various publishers

Essentially, the database will be used once only once a quarter, but already has turned a 5+ day procedure into a 1-day data entry blitz. They put in the book info, choose a prof, choose a class, and select the publisher (80 publishers' contact informations already set).

Once all the data is in, they can either open up the report to print out all the required letters/faxes/phone scripts per the publisher/book/course combo or open up another report which is just the bookstore order in the preferred bookstore order format (which they used to fill out with a typewriter).

Given my experience with Filemaker Pro, it's a very easy concept. However, given that this is my FIRST Access database, I've been reprogramming my brain to accept the differences (no calculations fields, but queries and expressions for example).

The database itself is complete now. However, as with any standard report, ALL records are displayed and will be printed if someone just hits "print". This poses a problem as there are a couple hundred entries per quarter. It would be much better if I could FILTER the report records by Quarter and Year.

Without a filter of sorts, they would have to quarterly delete all records (except publishers, I'd kill myself if they deleted all my research there) and then do their data entry. That's not "bad" per se, because once that info has been printed and sent out, there's no use for it-- but I think a filter would be good nonetheless.

NOTE: As I've stated in a prior post once I pose a question, I get brave (reckless) and begin exploring more confidently than I had been before. I found the menu function "sort by form" which uses your form to filter records in the form you've created. Can this filter somehow be translated to the report output?

 

[Zigzag]

Ok, this makes a little more sense,

Here are the basic steps.

Create a Simple query based on the table containing your data (use the wizard for this)
After it is created open it up in design view,
In the criteria under [Academic Quarter] type
Like ([Please enter academic quarter])

And similar for year.

Now create a new report based on this query (once again use the wizard to make life easier)

Now when you open the report you will be prompted for the "academic quarter" & "year" the report should now show data that match's.

Let me know how you get on, or post back if you need more info.

Garry

 

[eepok]

It seems to have worked. I'm drop in some dummy data (book orders from another quarter/year combo) tomorrow to see if it really does the magic.

On a related note: Is it always better to base a report off a query as opposed to the table itself in case you want to drop such a function in?