Microsoft Access 3F: Report Parameters

Parameters in Queries:

• Look at the following query below which lists student numbers of students signed up for various courses.

figure 4.6.1

• You could enter BCA 11 under the ClassName field in the design view of the query as shown below to display only students enrolled in the class BCA 11.

Note: it is inconvenient to go into design view and change the criteria each time a user needs to look up information.

• Replacing BCA 11 with [Which class?] will create a pop-up window which asks the user to enter any class they want when the query is opened.

• Now when the query is opened the following pop up window will appear.

• The user is now free to enter any class name they want.

• If the user enters BCA 11 in the above pop up window the query displays the following:

Note: only those students enrolled in BCA 11 appear.

• After closing and opening the query again, the pop up appears, this time, the user enters Data Management 12 in the pop up window.

• The following information then appears:

• Parameters are great for people who may be using the database and are not familiar with how to use queries in design view. You simply need to train someone to click on the appropriate query and simply enter the parameter.

• The problem with a parameter is that the user must key in the "exact" match, with no spelling mistakes or missing spaces etc.

  Example: if the user entered in Data Management instead of Data Management 12 nothing would appear. If the user entered DataManagement 12 or Data Management12 the spacing would not match and nothing would appear.

  Note: look at the figure below...when the origninal query (without parameters) was displayed at the beginning of this lesson the ClassName appears as Data Management (not Data Management 12), however this is only because the field width is too short to display the entire name.

  You will notice that Business Info Management 12 is also "cut-off". Be careful to always use the entire correct field name and make check that field names in columns are wide enough to view the entire name.

  

• You could change the parameter in the query to the following:

Note: the parameter must always be in square brackets [ ], this time the parameter was placed in the StudentNumber field.

How many records would be displayed if the user entered the following in the pop-up window? (This will be one of the review questions for this assignment)



 

Parameters in Reports:

• So far all you have done is replaced a criteria with a question in square brackets in a query, however the title of this lesson is Report Parameters.

• Because all reports are based on tables or queries, the pop up window will appear when you open a report that is based on a query that has a parameter.

  Example: look at the following report based on a query (without parameters), it is grouped by StudentNumber

  

• If you add a parameter to the query which this report is based on the following pop up window appears when you open the report and allows the user to enter a student number as shown:

• The following report is displayed:

Note: only the classes for the requested student are displayed in the report.

Changing Where a Report Gets Information From:

• Remembert that a report is based on a query or a table, however sometimes you may want to make copies of a report structure and base them on a different table or query.

• If you have made a lot of changes to the report heading, adding logos etc. you don't want to have to do this for every new report that you create.

• Instead of creating a new report each time (and adding a logo and other changes) you can simply make a copy of the report and change where the report gets the information from (for example change it from one query to another).

This website is copyright protected see contact page for details.