Parameter Queries in MS Access

A parameter query is a query that displays an input prompt and uses the value entered by the user as a criterion.

Example: Students Table

Suppose you want to find students by course. Instead of writing a separate query for ADCA, Tally Prime, and Web Development, you can create one parameter query.

Follow these steps to create a parameter query:

1. Open your MS Access database.
2. Click the Create tab.
3. Select Query Design.
4. Add the Students table.
5. Add the fields you want to display.
6. In the Criteria row under Course, enter [Enter Course Name:].
7. Click Run (!).
8. Enter a course name when Access displays the prompt.
9. Click OK to view the matching records.
10. Save the query as qryStudentsByCourse.

Criteria Example

[Enter Course Name:]

When the query runs, Access asks the user to enter a course name. If the user enters ADCA, records for that course are displayed.

You can create a parameter query directly in SQL View.

Example: Search Students by Course

PARAMETERS [Enter Course Name:] Text (255);

SELECT *
FROM Students
WHERE Course = [Enter Course Name:];

The PARAMETERS declaration identifies the input parameter as text. Access prompts for its value when the query runs.

Example: Search by Student Name

PARAMETERS [Enter Student Name:] Text (255);

SELECT *
FROM Students
WHERE StudentName = [Enter Student Name:];

Enter a student's name to display the matching record.

You can also prompt the user to enter a number, such as a minimum fee amount.

Example: Find Students Above a Fee Amount

PARAMETERS [Enter Minimum Fees:] Currency;

SELECT StudentName, Fees
FROM Students
WHERE Fees > [Enter Minimum Fees:];

If the user enters 5000, the query returns students whose fees are greater than 5000.

Example: Find Students at or Above a Minimum Fee

PARAMETERS [Enter Minimum Fees:] Currency;

SELECT StudentName, Fees
FROM Students
WHERE Fees >= [Enter Minimum Fees:];

This version also includes students whose fees are exactly equal to the entered amount.

You can use more than one parameter in a single query.

Example: Search by Course and Minimum Fees

PARAMETERS
    [Enter Course Name:] Text (255),
    [Enter Minimum Fees:] Currency;

SELECT StudentName, Course, Fees
FROM Students
WHERE Course = [Enter Course Name:]
AND Fees >= [Enter Minimum Fees:];

When this query runs, Access prompts for:

• The course name.
• The minimum fee amount.

If the user enters ADCA and 5000, the query returns ADCA students whose fees are at least 5000.

Parameter queries can ask the user to enter a date. This is useful for searching admission records or transactions within a date range.

Example: Search by Admission Date

PARAMETERS [Enter Admission Date:] DateTime;

SELECT StudentName, AdmissionDate
FROM Students
WHERE AdmissionDate = [Enter Admission Date:];

The user enters a date and Access displays records matching that date. This exact comparison is most suitable when the field contains dates without different time components.

Example: Search Between Two Dates

PARAMETERS
    [Enter Start Date:] DateTime,
    [Enter End Date:] DateTime;

SELECT StudentName, AdmissionDate
FROM Students
WHERE AdmissionDate >= [Enter Start Date:]
AND AdmissionDate < [Enter End Date:] + 1;

If the user enters a start date and an end date, this pattern includes the full end date when the field contains times as well as dates.

You can use a parameter with LIKE to search for names that contain or begin with user-entered text.

Example: Search Names Beginning with Entered Text

PARAMETERS [Enter Starting Letters:] Text (255);

SELECT StudentName, Course
FROM Students
WHERE StudentName LIKE [Enter Starting Letters:] & "*";

If the user enters A, the query finds names beginning with A in Access's default ANSI-89 wildcard syntax.

Example: Search for Text Anywhere in a Name

PARAMETERS [Enter Text:] Text (255);

SELECT StudentName, Course
FROM Students
WHERE StudentName LIKE "*" & [Enter Text:] & "*";

If the user enters "an", the query searches for names containing that text sequence.

• Reusable: Run the same query with different values.
• Flexible: Search by name, course, fees, or date.
• Time-saving: Avoid creating many similar queries.
• User-friendly: Prompts guide users to supply search values.
• Consistent: Use the same filtering logic every time.

Example

A school administrator can use one parameter query to search for students in ADCA today and Tally Prime tomorrow. There is no need to create a separate query for every course.

Create a table named Students with these fields:

• StudentID — AutoNumber
• StudentName — Short Text
• Course — Short Text
• Fees — Currency
• AdmissionDate — Date/Time

Tasks

1. Enter at least ten student records.
2. Create a parameter query to search by course name.
3. Create a parameter query to find students by name.
4. Find students whose fees exceed an entered amount.
5. Create a query with course and minimum-fee parameters.
6. Create a query that searches by admission date.
7. Create a query that searches names beginning with entered letters.
8. Save the queries with meaningful names.
9. Run each query using at least two different input values.

Goal: Learn to create reusable parameter queries with text, numeric, and date inputs.

1. What is a parameter query?
2. How does a parameter query receive input?
3. How do you create a parameter query in Design View?
4. How can you declare a parameter in SQL View?
5. How do you search for students by course name?
6. How can you use two parameters in one query?
7. How can a parameter query search a date range?
8. What should you check if Access displays an unexpected parameter prompt?