Create a parameter query to select the tblMember table records for a City field value that the user specifies

Create a parameter query to select the tblMember table records for a City field value that the user specifies. Select all records from the table if the user doesn’t enter a City field value. Display all fields from the tblMember table in the query record set. Save the query as qryMemberCityParameter.

The Correct Answer and Explanation is:

Certainly! Here’s the correct parameter query for MS Access to select records from the tblMember table based on the City field, with the ability to display all records if no City is specified:

PARAMETERS [Enter City:] Text ( 255 );
SELECT *
FROM tblMember
WHERE ([City] = [Enter City:] OR [Enter City:] IS NULL OR [Enter City:] = '');

---

This query is designed to prompt the user to enter a City name when it runs. The key parts of the query include:

1. PARAMETERS Clause:

   PARAMETERS [Enter City:] Text (255);

This clause declares a parameter named [Enter City:] of type Text with a maximum length of 255 characters. When you run the query, Access will pop up a prompt box asking the user to “Enter City:”.

2. SELECT * FROM tblMember This selects all fields from the tblMember table, which fulfills the requirement to display all fields.
3. WHERE Clause Logic:

   WHERE ([City] = [Enter City:] OR [Enter City:] IS NULL OR [Enter City:] = '');

This is the crucial part for flexibility:

• [City] = [Enter City:] filters the records to only those whose City exactly matches what the user entered.
• [Enter City:] IS NULL allows for cases where the user cancels the prompt or enters nothing, so the filter does not exclude any records.
• [Enter City:] = '' handles the case when the user leaves the input empty (presses OK without typing anything). This also causes the query to show all records.

Why this works:

• When the user enters a city name, only records with that city appear.
• When the user does not enter a city (empty string) or cancels, the WHERE condition still evaluates to true for all records because the OR conditions allow the filter to effectively be bypassed.

How to save the query:

1. Open MS Access.
2. Go to the Query Design view.
3. Close the Add Table dialog without adding any table.
4. Switch to SQL View.
5. Paste the query above.
6. Save the query as qryMemberCityParameter.