Show Both Yes and No Results in a Microsoft Access Query
Question details
The user needs a Microsoft Access query to calculate eligibility with an IIf expression and return both eligible and ineligible records, instead of only qualifying participants.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Building a query that classifies participants as eligible or ineligible using an IIf expression and displaying both Yes and No results.
- Observed behavior
- The query currently returns only qualifying participants (Yes/True) because Yes or True criteria filter out the records that should display No.
Open the query in Design View or SQL View and verify that the IIf expression is placed in the SELECT field list, not in the criteria row. Check the criteria row for any Yes, True, =Yes, or =True entries that may be filtering the result.
Remove Yes or True Criteria from the Access Query
Use this solution when a Microsoft Access IIf query returns only qualifying rows because the criteria row still contains Yes or True filters.
The IIf expression in the SELECT list already evaluates each record and returns "yes" or "no". A criteria row that filters for Yes, True, or qualifying values will hide the records that should display No, so the criteria must be removed or moved to unrelated fields only.
In Microsoft Access, open the database and right-click the query in the Navigation Pane, then choose Design View.
Find the column that contains the IIf([ADL assistance] Or [Bed bound r/t illness] Or [Nursing Home] Or [Eyesight < 5/200], "yes", "no") expression.
Click the Criteria row under that IIf column and remove any entry such as Yes, True, =Yes, =True, or similar qualifying filters.
If you still need filters for other fields, enter them in the criteria row of those other columns or in the SQL WHERE clause, not under the IIf column.
Click Run or Datasheet View and confirm that both yes and no rows appear.
Verify the SQL Statement in SQL View
Use this solution if Design View is unclear or if you want to confirm the IIf calculation is in the SELECT list and no Yes/True filter remains in the SQL.
Use WPS Office as a Free, Lightweight Alternative for Daily Office Documents
WPS Office does not include a Microsoft Access database engine, so Access query behavior must be fixed in Microsoft Access. For spreadsheet, document, and presentation work, WPS Office is a free, lightweight, easy-to-use Office alternative with a familiar ribbon interface and strong Microsoft Office format compatibility, making migration from Excel, Word, and PowerPoint straightforward.
- 1. Download WPS Office: Click Download WPS Office Free and save the installer from the official WPS site.
- 2. Install and open WPS Office: Run the installer and open WPS Office to see the home screen.
- 3. Open Microsoft Excel files: Use WPS Spreadsheets to open .xlsx files and continue eligibility calculations with familiar functions.
- 4. Use the ribbon tabs: Use the Home, Formulas, Data, and Review tabs to find tools similar to Microsoft Office.
Frequently Asked Questions
Why does my Access IIf query show only Yes records?
The IIf expression returns both "yes" and "no", but a criteria row containing Yes, True, or =Yes under the calculated column filters out the No records. Open the query in Design View, remove that criteria, and run the query again.
How do I remove criteria from an Access query in Design View?
Open the query in Design View, locate the column with the IIf expression, click the Criteria row under that column, and delete the Yes, True, =Yes, or =True entry.
Can I keep a WHERE clause in an Access query with an IIf calculation?
Yes. Keep the IIf expression in the SELECT list and use the WHERE clause only for unrelated fields. Avoid filtering the calculated eligibility column with WHERE IIf(...)=True or HAVING IIf(...)=True if you want both yes and no results.
Does WPS Office include Microsoft Access?
No. WPS Office does not have a Microsoft Access database application or an .accdb database engine. You need Microsoft Access to run Access queries, but you can use WPS Office for Excel, Word, and PowerPoint files.
Can I open an Access database in WPS Office?
No, WPS Office does not open .accdb or .mdb database files. For Access-specific query work, use Microsoft Access; for spreadsheet and document tasks, WPS Office offers free Microsoft Office file compatibility.
