MS Access Database: Filter Data for a Report

A common technique that I use to make a more professional looking user interface for filtering data for a report in an MS Access database, is to create a form to ask for criteria for the report. One example would be a report that is used to show data for a particular time period.

Step 1
Create a query to feed data to the report. Add any necessary 
parameters (like the “between [start_date] and [end_date]” style) and test.

Step 2
Create the report, using the query from step 1 as the record 
source. Test the report thoroughly. Obviously, you will have to 
answer the parameter questions manually, one at a time.

Step 3
Create a form with a text box for the starting date and another 
text box for the ending date. Use the button wizard to add a 
button that will preview the report created in step 2.

Step 4
Change the parameters in the query to this style:

Between [forms]![myform]![txtStartDate] and [forms]![myform]![txtEndDate]

making sure you use the actual names for the form “myform” and the text boxes “txtStartDate” and “txtEndDate”.

That’s it. And, no VBA coding was required.

(you may need to put some code behind the button that will verify the values in the various fields before starting the report, this would require VBA – you could also use a combo box to collect data for the report criteria)

Richard Killey is a Computer Database Programmer specializing in MS Access database development. Visit http://www.databaselessons.com to read other articles of interest, and to download sample MS Access databases.

Article Source: http://EzineArticles.com/?expert=Richard_Killey

This is the principle of how to use an Access Form as the parameter input instead of the direct and raw prompt that an Access Query provides.

If you are going to use a Form, it makes sense to have various controls to make this a more flexible input form (several parameter options) that makes this in essence a criteria search tool for a report.

If you do this however, you increase the likely hood of using either and Access Macro or VBA code to enhance and validate data entry values and control navigation.

The question you must ask to justify whether you stick with the standard ‘out of the box‘ Access Query prompt or opt for the richer but user-developed Access Form is “Are you going to be the sole user or will they be multiple users involved who are not so Microsoft Access savvy?


This entry was posted in Forms, Macros, MS Access, Queries, Reports, VBA and tagged , , , , . Bookmark the permalink.

One Response to MS Access Database: Filter Data for a Report

  1. Alex says:

    Hi!
    I was on the Microsoft website. The request – which is evoked – don’t seem to be told as needing to be created. And I am “familiar” now, to Access. So I had to repeat their proceeding, but without success.

    Your simple procedure in four steps speek to me. It appears that a request is necessary.
    I could repeat this, and will now see if this can be done in my databse with my instruction.

    For the fact that I could do your procedure: a great Thank you !

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.