Others

Fix Access Query Ignoring Is Not Null for Checkbox Fields

WPS Content Manager Oct 10, 2026 869 views

Question details

The user is attempting to filter unchecked records in a Microsoft Access query using the 'Is Not Null' criterion, but the query fails to exclude the unchecked checkbox records.

How to Fix Access Query Ignoring Is Not Null for Checkbox Fields

Product: Microsoft Access
Device & OS: not provided
Scenario: Applying criteria to a Yes/No checkbox field in an Access database query to filter out specific records.
Observed behavior: The 'Is Not Null' condition is ignored by the query because standard Yes/No fields do not contain Null values, resulting in an unfiltered or incorrect dataset.

Before you start

Verify whether your checkbox field is bound to a standard Yes/No data type or a nullable numeric field, as this dictates which query criteria will actually work.

Solution 1Recommended

Use TRUE or FALSE Criteria for Standard Yes/No Fields

Since standard Yes/No fields in Access cannot naturally be Null, you must explicitly filter using True or False criteria instead of Is Not Null.

A standard Yes/No field in Microsoft Access is designed to only contain boolean values: True (-1) or False (0). Because these fields are never genuinely empty (Null), using 'Is Not Null' will evaluate to true for every single record, failing to filter out the unchecked boxes.

Open your query in Design View

Right-click the query in the Access navigation pane and select 'Design View' from the context menu.

Locate the checkbox field

Find the specific Yes/No column in the query design grid at the bottom of the screen.

Apply the correct boolean criteria

In the 'Criteria' row directly under your checkbox field, type 'TRUE' to display only checked records, or type 'FALSE' to display only unchecked records. Ensure you remove the 'Is Not Null' text.

Run the updated query

Click the 'Run' button (the red exclamation mark) in the Query Design ribbon to view the accurately filtered results.

Use TRUE or FALSE Criteria for Standard Yes/No Fields

Alternative Syntax: You can also use '-1' instead of 'TRUE' and '0' instead of 'FALSE'. Access interprets these numeric values identically to their text counterparts.

Solution 2

Enable the TripleState Property for Nullable Checkboxes

If your database logic specifically requires an 'unknown' or Null state, you must bind the checkbox to a numeric field and enable the TripleState property.

Free Microsoft Office alternative

Switch to WPS Office for a Lightweight and Free Office Experience

While WPS Office does not include a direct database management tool like Microsoft Access, it serves as an exceptional, free alternative for your daily word processing, spreadsheet, and presentation tasks. Enjoy high compatibility with Microsoft Office formats in a single, lightweight application.

1. Download the WPS Office installer: Visit the official WPS Office website and click the free download button for your specific operating system.
2. Install the suite: Run the downloaded installer file and follow the straightforward on-screen instructions to set up the software.
3. Open your existing documents: Launch WPS Office and directly open your existing Word, Excel, or PowerPoint files without losing any formatting.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) file formats.Lightweight architecture ensures quick installation and smooth performance on older devices.Familiar user interface requires no learning curve for Microsoft Office users.Completely free to use with integrated PDF editing and conversion tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access query return all records when I use 'Is Not Null' on a Yes/No field?

Standard Yes/No fields in Access are designed strictly to hold True (-1) or False (0) values. Because they cannot inherently contain a Null value, the 'Is Not Null' condition evaluates to true for every single record, effectively bypassing the filter entirely.

How do I query only the checked boxes in Access?

To filter for checked boxes, open your query in Design View, locate your Yes/No field, and type TRUE (or -1) into the Criteria row. To find unchecked boxes, type FALSE (or 0).

What does the Triple State property do for a checkbox?

The Triple State property allows a checkbox control on a form to visually display three distinct states: checked (True), unchecked (False), and greyed out (Null). This property only functions correctly if the checkbox is bound to a nullable field type, such as a Number, rather than a strict Yes/No field.

Office Quick Solutions