How to Query Microsoft Access for a Blank Field
An empty field in a database can cause unique problems. It will not come up in any query that uses a comparison or wild card operator. As far as the database is concerned, there is nothing there to use for comparing, sorting or responding to any type of query. This type of entry is referred to as null. Nonetheless, there are ways to query Microsoft Access for a blank field.
Open the database that holds the table with the field you want to search. Click on the "Queries" object on the left and click "New" on the menu. Highlight "Design View" and click "OK." Highlight the table that holds the field and click "Add." Click "Close," then click "View" on the main menu and switch to "SQL View." A partial query will already be in the window: "Select from tablename," where tablename is the table you want to query.
Enter a "*" after the "Select" statement to return all fields in the query. Place the cursor before the ";" and press "Enter" to start a new line. Type "Where fieldname is null" replacing fieldname with the name of the field you want to check for blanks. Click on the red exclamation point on the toolbar to execute the query. All rows where your field is blank should be displayed in the results.
Test for an empty string. This is not the same as a blank field, but can appear as such in the results. To test for an empty string, run the same query as step 2, but replace the where clause with Where fieldname = ''"Note, the two double quotes after the equal sign are part of the query.
Tips & Warnings
- If blank fields will create a problem, they can be prevented in the database design. When defining a field, Change "Required" to "Yes" in the "Field Properties" view to prevent a record with a blank field from being entered.
- Always test for blank fields before adding any code that would manipulate a field. Any function called on a field that is null will generate an error.