How to Design Yes, No, and N/A Fields in Microsoft Access
Question details
The user needs to create a database field that allows three specific responses (Yes, No, and N/A), but is encountering limitations with the default Access field types.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing a questionnaire, survey, or data-entry form where respondents must be able to select from exactly three options: Yes, No, or Not Applicable.
- Observed behavior
- The standard Yes/No data type in Microsoft Access only supports two binary values (True/False or Yes/No) and cannot accommodate a third N/A option.
Understand that the native Yes/No data type in Access is strictly binary. You will need to change the field's data type to Short Text or Number to accommodate three choices.
Create a Value List Combo Box on the Data-Entry Form
Bypass the binary Yes/No field by using a standard text field and controlling the allowed inputs with a combo box directly on your form.
It is a best practice in Microsoft Access to avoid using lookup fields directly in your tables. Instead, you should store the raw data in the table and handle the user interface, including drop-down combo boxes, exclusively on your data-entry forms.
Open your Access table in Design View. Change the Data Type of your target response field from 'Yes/No' to 'Short Text'. Save and close the table.
Open your data-entry form in Design View. Delete the old checkbox control if one exists, and drag a new 'Combo Box' control onto the form, binding it to your modified response field.
Select the Combo Box and open the Property Sheet. Set the 'Row Source Type' property to 'Value List'.
In the 'Row Source' property field, manually type: "Yes";"No";"N/A". Save the form and switch to Form View to test the drop-down.
Implement a Normalized Lookup Table Structure
For complex questionnaires with multiple questions, establish a dedicated lookup table for responses to ensure scalable database normalization.
Handle Data and Surveys Easily with WPS Office
If setting up normalized relational databases in Microsoft Access feels overly complex for a simple survey, consider using WPS Spreadsheet. As a free, lightweight, and highly compatible Microsoft Office alternative, WPS Spreadsheet lets you manage questionnaires effortlessly using intuitive data validation tools and familiar interfaces.
- 1. Select Cells: Open WPS Spreadsheet and highlight the cells where you want the Yes/No/N/A responses.
- 2. Open Data Validation: Navigate to the 'Data' tab and click on 'Validation'.
- 3. Create the Drop-down List: Under the 'Allow' drop-down, select 'List'. In the 'Source' box, simply type 'Yes,No,N/A' and click OK.
Frequently Asked Questions
Why can't I just add 'N/A' to the default Yes/No field in Access?
The Yes/No data type in Microsoft Access is strictly a boolean field. At the database level, it only stores a -1 for Yes (True) or a 0 for No (False). It physically lacks the capacity to store a third state, which is why a Short Text or Number field is required for multiple choices.
What is wrong with using a Lookup Wizard directly in an Access table?
While the Lookup Wizard seems convenient, database professionals advise against table-level lookups. They mask the actual data being stored (showing text instead of the underlying ID), which can cause significant confusion when writing queries, running reports, or migrating the database to SQL Server.
How do I make my Access questionnaire handle multiple answers?
To allow multiple answers to a single question, you must design a many-to-many relationship using a junction table. This involves a table for Respondents, a table for Questions, and a junction table linking RespondentID, QuestionID, and ResponseID.
