INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access 101

Null Values and How Access/Jet deals with them. by jrbarnett
Posted: 12 Jul 03

What Is "Null"?
A field in a table, or control on a form or report which does not have a specific value entered in it is said to have a Null value. This is different from a default value as 0 on a numeric field or an empty string in a text field or string variable.

The meaning of a null value within a table depends upon the context of the table, but most of the time it is an optional field that hasn't had anything entered into it for that particular record.

Null values in variables or controls on a form/report will cause some functions to fall over with a runtime error, because they are expecting a value to work on. For example, if txtCustomerID is Null, this following code will cause the debugger to open and the cursor to highlight this line of code:

MsgBox "The length of CustomerID is " & Len (Me!txtCustomerID), vbInformation+vbOkOnly

In code, you cannot check for a value being equal to Null, as you can any other value, you need to check whether a field Is Null.  
The method of picking it up is the same regardless of the field's data type in the underlying table.

With SQL code it is done thus:

Select Fieldname1, Fieldname2, Fieldname3
From Tablename
Where Fieldname4 Is Null;

or using VBA with the IsNull function. Thus:

If IsNull (Me.txtTextbox) Then
  MsgBox "txtTextbox Is Null", vbOkOnly+vbInformation
End If

These conditions can be inverted, so you can check for fields where they are not null thus:

Select Fieldname1, Fieldname2, Fieldname3
From Tablename
Where Fieldname4 Is Not Null;

and:

If Not IsNull (Me.txtTextbox) Then
  MsgBox "txtTextbox Is Not Null", vbOkOnly+vbInformation
End If


Null Values in variables and form/report fields
The only type of variable that can hold a Null value is a Variant, as strings are initialised to an empty string ("") and numeric ones (including Yes/No) are initialised to 0 (No/False is stored as 0; True/Yes is stored as -1).

Within VBA code, null values can cause problems when manipulating the contents of text fields and textboxes, so precautions have to be taken.

A nifty way of getting around this problem in text boxes and fields for holding text values is to append an empty string to it and check that the length of this is greater than 0. Many of my VBA code postings use this construct:

If Len (Me.txtTextfield & "") > 0 Then
   MsgBox "txtTextfield is not null", vbInformation+vbOkOnly  ' value is not null
Else
   MsgBox "txtTextfield is null", vbInformation+vbOkOnly
End If

This works because null appended to an empty string is an empty string, and an empty string has a length of 0, so if there is a value in the textbox, it won't affect the output.

Null values in numeric fields
Null values in database fields and variables that store numeric values are ignored when using Group By queries and aggregate SQL functions such as Sum, Avg, Count and Var, as well as the equivalent domain functions DSum, DAvg, DCount and DVar.

This can cause unexpected results in the output. For example:

With a data set of 10, 20, null, 40

Avg and DAvg of the data is 23.333 recurring, ie Access ignores the Null value, so the calculation is (10+20+40)/3

Count/DCount returns 3 (ditto)
Sum/DSum of the data however is 70 as you would expect.

Performing calculations with null values is another area which can cause problems for the unwary. If there is the possibility of one value being Null, the Nz function can be used to substitute a value to use in case it is to stop the calculation returning Null. Thus:

intResult = 100 * Nz(Me!txtTextbox, 1)

This function call will return 1 if the value of txtTextbox on the current form is null.

A reasonable analogy in terms of calculations is that if you imagine Null as the same as infinity you won't get too far wrong, in that you will always get null as the output.  

Avoiding Nulls
The easiest way of avoiding Null values in the first place is to ensure that the database fields are set to require entry in those fields where it is required, and that code in forms is set to display an appropriate error message for those fields where entry is required, if the user tries to bypass entering information.

Summary
Null values are something that database professionals (designers, developers and DBA's) will deal with on a daily basis and ensuring that their code can cope with them is an essential part of their knowledge.  By using the information here it shouldn't be too difficult to write robust applications that don't have problems dealing with them.

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close