Query Criteria in MS Access

Query criteria are expressions that determine which records appear in the query results. Records that satisfy the conditions are displayed.

Example: Students Table

If you enter "ADCA" in the Course field's Criteria row, the query displays Amit and Rahul.

1. Open your database in Microsoft Access.
2. Click the Create tab.
3. Select Query Design.
4. Add the table containing your data.
5. Add the required fields to the query grid.
6. Enter a condition in the Criteria row under the appropriate field.
7. Click Run (!) to view the matching records.
8. Save the query if you want to use it again.

Example: Find ADCA Students

This query displays students whose Course field contains the value ADCA.

Comparison operators allow you to compare field values with a specified value.

Example: Fees Greater Than 5000

SELECT StudentName, Fees
FROM Students
WHERE Fees > 5000;

This query displays students whose fees are greater than 5000.

Example: Fees Not Equal to 10000

SELECT StudentName, Fees
FROM Students
WHERE Fees <> 10000;

This query returns students whose fees are not equal to 10000.

Text criteria are used to search fields containing names, course names, cities, and other text values.

Exact Text Match

SELECT *
FROM Students
WHERE Course = "ADCA";

This query finds students enrolled in ADCA.

Find Students Whose Names Start with A

SELECT *
FROM Students
WHERE StudentName LIKE "A*";

In the default Access ANSI-89 pattern syntax, * matches zero or more characters. The query finds names beginning with A.

Find Names Containing "an"

SELECT *
FROM Students
WHERE StudentName LIKE "*an*";

This query finds names containing the character sequence "an".

The AND operator is used when all specified conditions must be true.

Example: ADCA Students with Fees Above 5000

SELECT *
FROM Students
WHERE Course = "ADCA"
AND Fees > 5000;

The query returns only students who are enrolled in ADCA and have fees greater than 5000.

Example: Fees Between 5000 and 10000

SELECT *
FROM Students
WHERE Fees >= 5000
AND Fees <= 10000;

This query includes students whose fees are at least 5000 and no more than 10000.

The OR operator allows a record to match at least one of the specified conditions.

Example: ADCA or Tally Prime Students

SELECT *
FROM Students
WHERE Course = "ADCA"
OR Course = "Tally Prime";

This query displays students enrolled in either ADCA or Tally Prime.

Example: Fees Below 5000 or Above 10000

SELECT *
FROM Students
WHERE Fees < 5000
OR Fees > 10000;

This query finds students whose fees are outside the range from 5000 to 10000.

BETWEEN selects values within a specified range, including both endpoints.

Example: Fees Between 4000 and 10000

SELECT StudentName, Fees
FROM Students
WHERE Fees BETWEEN 4000 AND 10000;

This query includes students with fees of 4000, 10000, and values between them.

Example: Admission Dates Within a Range

SELECT StudentName, AdmissionDate
FROM Students
WHERE AdmissionDate BETWEEN #1/1/2026# AND #12/31/2026#;

This example finds records with admission dates in calendar year 2026. It assumes AdmissionDate is a Date/Time field.

IN allows you to match a field against a list of specified values.

Example: Find Selected Courses

SELECT *
FROM Students
WHERE Course IN ("ADCA", "Tally Prime", "Python");

This query returns students whose course is ADCA, Tally Prime, or Python.

IN can make a query shorter and easier to read than writing multiple OR conditions.

NULL indicates that a field has no stored value. It is different from zero or an empty text string.

Find Records with Missing Course Values

SELECT *
FROM Students
WHERE Course IS NULL;

This query finds records where Course has a Null value.

Find Records with a Course Value

SELECT *
FROM Students
WHERE Course IS NOT NULL;

This query returns records where Course is not Null.

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. Find students enrolled in ADCA.
3. Find students whose fees are greater than 5000.
4. Find students whose fees are between 4000 and 10000.
5. Find students whose names begin with A.
6. Find students enrolled in ADCA or Tally Prime.
7. Find students admitted during 2026.
8. Find records where the Course field is Null.
9. Save each query with a meaningful name.

Goal: Practice text, numeric, date, range, and multiple-condition criteria in MS Access.

1. What are query criteria?
2. How do you find records with fees greater than 5000?
3. What is the difference between AND and OR?
4. What does the BETWEEN operator do?
5. What is the purpose of the IN operator?
6. How do you find names beginning with A?
7. What is the difference between NULL and zero?
8. How do you find records with missing values?