This Tutorial show you how to understand null values in ms access 2010, convert null values into meaningful values, how to avoid using null values in calculations in MS Access 2010 and give you hints of how to fix runtime errors associated with handling nulls in tables, forms, your vba and sql code. This tutorial would be useful if would like to fix errors: 2393 – You can’t set the IgnoreNull property of primary key to Yes, 3058 – Index of Primary key contain a Null value, error 3162 You tried to assign the Null Value to variable that is not a Variant data type and other errors in regards to null values.

Before you start working with data in MS Access 2010, please make sure you begin with the following 2 steps of the tutorial.

STEP 1 : Understand Null Value in MS Access 2010

Most of the time Null Value means the missing or unknown data. In your database you would not be able to compare Null Value to Anything. Trying to do so is the cause of many errors and frustrations. Let’s look at the example below:

A = null
B = null
C =3

If you compare A = B. This would return an error.
A<>B (returns an error)
A=C (returns an error)
A<>C (returns an error)

Try It yourself. MS Access 2010 gives error because: Null means Unknown, and if you compare anything to Unknown, Is is equal to it?
Ms Access 2010 responds: ” I do not know”

STEP 2: To Store or not To Store Nulls?

When creating a fillable form for the user, allow the user an option of leaving some fields blank (if it makes sense).

Then handle nulls at the table level:

a)        When setting up field properties, set the field’s Required property to “No”. You have allowed for table to store a null value.  In this case the user has the flexibility of not entering the value in that particular field.

b)       However, in my experience it is always better to store some symbol there that would mean “null value”. As development progresses you would find it useful not to have null values that can generate errors when used in functions for example. Therefore, set the Default property of the field to:

If Date – Date ()
If Text – “N/A” or “PENDING”
If number – 99999

Just document it well in your manual that these values mean NULL values (information has not been entered)

Drawbacks: You can only apply the default property to new records but not to existing ones

I hope these tricks above would help you to avoid :
Error 3058 “Index or primary key can’t contain a Null Value”
Error 3162 (You tried to assign the Null value to a variable that is not a Variant data
type)

It is essential to understand what is null value and know how to fix Ms Access 2010 errors surrounding the storing of nulls

My next post would explain of how to work with null values in MS Access 2010, find and exclude nulls if you wish using VBA DAO and ADO objects, how to avoid inconsistencies in SQL queries.