Others

How to Update All Records in a Microsoft Access Yes/No Field

Emma Brown Oct 15, 2026 869 views

Question details

The user needs to know how to correctly format and execute an update query to change the value of a Yes/No field for all records in a Microsoft Access table.

How to Update All Records in an Access Yes/No Field

Product: Microsoft Access
Device & OS: not provided
Scenario: Running a bulk SQL update operation to change a Yes/No field's value across all rows in a specific table.
Observed behavior: Access Yes/No fields natively store Boolean numeric values (0 for No, -1 for Yes) rather than text strings, which requires specific syntax when using an SQL UPDATE statement.

Before you start

Verify that your target field is formatted as a true Yes/No Boolean data type in the table's Design View, rather than a Short Text field storing the text word 'Yes'.

Solution 1Recommended

Execute a SQL Update Query with Boolean Values

Use a standard SQL UPDATE statement utilizing Boolean keywords (True/False) without quotation marks to update the field accurately.

Microsoft Access stores Yes/No data types as logical Boolean values. Internally, 0 represents 'No' (or False) and -1 represents 'Yes' (or True). Because these are mathematical or logical values rather than text strings, you must avoid wrapping your parameters in quotation marks during the query. Using quotes will cause Access to interpret the value as text, leading to a data type mismatch.

Open Query Design

Open your Microsoft Access database, navigate to the 'Create' tab on the top ribbon, and click on 'Query Design'. If the 'Show Table' dialog appears, close it without selecting anything.

Switch to SQL View

Click the View toggle button in the top-left corner of the Query Design ribbon and select 'SQL View' from the dropdown menu.

Enter the Update Statement

Type the SQL command: UPDATE YourTable SET YourField = True; (Replace 'YourTable' and 'YourField' with the actual names used in your database).

Run the Query

Click the red exclamation mark icon labeled 'Run' in the Design ribbon. Confirm the prompt to update the rows and apply the change to all records.

Execute a SQL Update Query with Boolean Values

Alternative Syntax: You can also use the numerical equivalents instead of True/False. For example, using SET YourField = -1; achieves the exact same result as setting the field to True.

Free Microsoft Office alternative

Need a Lightweight, Free Office Alternative? Try WPS Office

While WPS Office does not include a direct database management tool like Microsoft Access, it serves as a powerful, free alternative for all your Word, Excel, PowerPoint, and PDF needs. It provides a familiar interface and excellent format compatibility for users looking for an alternative to expensive Office subscriptions.

1. Download the Installer: Visit the official WPS Office website and click the free download button for your operating system.
2. Install the Software: Run the downloaded executable file and follow the straightforward on-screen installation prompts.
3. Start Creating: Open WPS Office to instantly create, edit, and save Word, Excel, and PowerPoint documents with full format compatibility.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx).Lightweight design that runs smoothly on all major operating systems.Familiar, intuitive user interface for seamless migration from Microsoft Office.Built-in robust PDF editing, merging, and conversion tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my update query give a 'Data type mismatch in criteria expression' error?

This error typically occurs if you put quotation marks around the word 'True' or 'Yes' in your SQL statement (e.g., SET Field = 'True'). Boolean values in Access must be entered strictly without quotes.

Can I use -1 and 0 instead of True and False in the Access query?

Yes. Microsoft Access natively stores 'Yes' as -1 and 'No' as 0 in the background. An update statement like UPDATE YourTable SET YourField = -1; works perfectly.

How do I verify if my field is a Boolean Yes/No or Short Text?

Right-click your table in the navigation pane and select 'Design View'. Look at the 'Data Type' column immediately next to your field name; it should explicitly read 'Yes/No' rather than 'Short Text'.

Will this UPDATE query affect every single row in the table?

Yes. Since the UPDATE statement provided does not include a WHERE clause to filter the data, it will forcefully apply the True or False value to that specific field for every record in the table.

Office Quick Solutions