Others

How to Count Yes or No Values in Microsoft Access

Steve K Oct 20, 2026 869 views

Question details

The user needs to calculate the total number of Yes or No responses within a specific Yes/No field in a Microsoft Access database.

How to Count Yes or No Values in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Performing data analysis or creating a query where a summary count of boolean (Yes/No) fields is required.
Observed behavior: The user needs the correct aggregate or domain formulas to calculate the total count of Yes, No, or Null values, depending on where the result will be displayed.

Before you start

Identify the exact table name and the Yes/No field name in your Access database, and check if your dataset contains Null (empty) values that need to be excluded from the count.

Solution 1Recommended

Using Aggregate Functions to Count Yes or No Values

Use the Sum function in a query to calculate the total number of Yes or No responses based on how Access stores boolean values.

In Microsoft Access, a 'Yes' value is internally stored as -1, and a 'No' value is stored as 0. By applying mathematical functions to these numbers, you can easily total the values in a query.

Open Query Design

Open your Access database, go to the 'Create' tab on the ribbon, and select 'Query Design'. Add the table containing your Yes/No field.

Count Yes Values

In a blank column of the query design grid, enter the expression: Sum(Abs([FieldName])), replacing [FieldName] with your actual field name. The Abs() function converts the -1 to 1, allowing it to be summed correctly.

Count No Values

If the field contains strictly Yes and No values (no Nulls), enter the following expression in another column to count the No values: Sum([FieldName] + 1).

Run the Query

Click the 'Run' button (the red exclamation mark) on the Design tab to view the total calculated counts.

Using Aggregate Functions to Count Yes or No Values

Handling Null Values: If your field contains Null values, the Sum([FieldName] + 1) formula may yield inaccurate results for 'No' counts. In such cases, use a conditional statement like Sum(IIf([FieldName]=0, 1, 0)) instead.

Solution 2

Using the DCount Function for Specific Criteria

Use the DCount domain function to count specific values directly from a table, which is especially useful for displaying totals on forms or reports.

Free Microsoft Office alternative

Need a Lightweight Office Suite for Your Data Exports?

While Microsoft Access handles complex relational databases, you can easily export your query results to WPS Spreadsheet for faster formatting, charting, and further analysis. WPS Office is a free, lightweight, and highly compatible alternative to Microsoft Office.

1. Export Data from Access: Right-click your query results in Microsoft Access and select Export > Excel to save your data as an .xlsx file.
2. Open in WPS Spreadsheet: Launch WPS Office and open the exported .xlsx file to view your Yes/No data.
3. Analyze the Data: Use the simple COUNTIF function in WPS Spreadsheet (e.g., =COUNTIF(A:A, "Yes")) to quickly analyze your boolean values.

Seamless compatibility with Microsoft Excel (.xlsx) formatsFree and lightweight alternative to Microsoft OfficeFamiliar, easy-to-use interface with no steep learning curveBuilt-in advanced formulas (like COUNTIF) for quick data counting

microsoft office alternative - wps office

Frequently Asked Questions

How does Microsoft Access store Yes/No values internally?

In Microsoft Access, a Yes (True) value is typically stored as -1, and a No (False) value is stored as 0. This is why wrapping the sum of Yes values in the Abs() function converts the negative sum into a positive count.

Can I count Null values in a Yes/No field?

Yes. If your field allows empty values, you can count the Null records by using a conditional aggregation function, such as Sum(IIf(IsNull([FieldName]), 1, 0)), which evaluates to 1 only when the field is empty.

Why use DCount instead of Sum for counting No values?

DCount is a domain aggregate function that allows you to embed criteria (like "[FieldName] = 0") directly within the function call. It is ideal for pulling isolated counts directly into text boxes on Forms or Reports without needing to build and run a separate summary query.

Office Quick Solutions