What does NULL mean? How is it different than the Empty String?

One database concept given to much misunderstanding is the NULL value. What is it?

Null means that the value is unknown. This is different from the Empty String. The Empty String ("") means we know what the value is, and the value is, well, empty. That is, we know that there ISN'T a value.

Consider the case of the middle initial. Some people have a middle initial and some don't. Of those that do, we may or may not know what it is. If I leave it Null, it says that I don't know whether they have one or not. However, I can set the field to "" (Empty string) which says that they DO NOT have a middle initial.

Why is this difference important? Primary keys, for one thing.

Let's assume that my primary key is a composite key composed of FirstName, MiddleInitial, LastName. A primary key CANNOT have a Null as a part of it, thus we can set the value of MiddleInitial to "", and it will accept it. But we cannot leave the value Null.

Why can't a Primary Key have a Null value? Well aside from the fact that Access won't let it, the fact that Null means "I don't know the value" means that it cannot positively guarentee that the field is unique, which is one of the properties of the primary key.

The following four subs illustrate the differences:
Sub test()
If Null = Null Then
MsgBox "True"
Else
MsgBox "false"
End If
End Sub

Running this sub will ALWAYS evaluate to False. Why? Because we don't know what the value of NULL is, so we can't say if it's equal to NULL.

Sub test2()
If IsNull(Null) Then
MsgBox "True"
Else
MsgBox "false"
End If
End Sub

This evaluates to True because the IsNull() is specifically designed to test for NULL.

Sub test3()
If "" = "" Then
MsgBox "True"
Else
MsgBox "false"
End If
End Sub

This evaluates to True because the empty string is a known value.

Sub test4()
If IsNull("") Then
MsgBox "True"
Else
MsgBox "false"
End If
End Sub

False because "" is known, therefore it cannot be unknown.

As I said, NULL=NULL always evaluates to False. Since we don't know the value of Null, we can't say what it equals. This is why you never use NULL this way, but always use the IsNull() function.

Further, Len(Null) does evaluates to Null, as indeed do most things you *compare* with or *do to* Null, ie. Null=Null, X=Null, Null=True, cint(Null), etc.

Len("") ("" representing the 'Empty String' or 'Zero Length String') evaluates to 0 (zero). Because the length of a string of zero length is zero (duh!, my daughter would say).

The problem with explaining NULL, is all the good words are used for something else. You can't define it with itself, so saying "Null is Null" is null content (forgive the expression). You can't use "nothing", because that is an object pointer used to de-allocate object variables. You can't use "empty", because that is a constant specifically for use with a Variant variable. You can't use Zero either, because that represents a specific numeric value, rather than the absence of a value.

I guess we'll have to blame E.F. Codd who declared that any relational database implementation must have a special value called "Null" which represents the "absence of anything" or “unknown” condition for all datatypes.

The best we can do is to say that the value of Null is 'we don't know', which is not to say that we don't know what Null is ... I'd better stop there.

Featured Sample: Really Bad Design Decisions

Really Bad Design Decisions

This is a case study of a really bad database design . Often, initial design decisions can have a cascading effect, creating multiple, secondary design errors.

One such error, commonly made by novice developers, is to slavishly follow a pre-existing paper form to determine their table design. But paper forms are not created with proper database design in mind. They are designed to make it easy for humans to fill out.

This design can be at odds with established design principles. By blindly following the paper form to determine the database design, the developer can create a system subject to numerous data and relational integrity errors.

Sample contains an explanatory document and two databases: "ReallyBadDatabase.mdb" and "ReallyBadDatabaseReborn.mdb". The former shows the initial design, the second shows an improved design. Sample include both the data model and application.

You can find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=291

Featured Sample: At Your Survey (AYS)

At Your Survey

Author: Duane Hookom

At Your Survey (AYS) is a full featured application that allows users to create their own surveys by designing the questions and providing a lookup of possible responses. ATS uses a fairly normalized table structure so the same tables, forms, code, queries, and reports can be used for any number of surveys. There is a brief manual to help you get started as well as a sample survey with data.

Find out more here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

Access 101: Can I Create an EXE from my Access Application?

There is no way to create an EXE from an Access database. However, there is a way to install what's known as the "Access Run-time engine" that will allow the user to run the app without having Access installed on their system. The Run-Time is NOT the same as an EXE. For one thing, anyone who has Access on their system will still be able to open the application in their copy of Access (assuming they have the right version).

The Run-Time engine is installed as an option in the Package and Deployment Wizard. In Access 2000, this wizard came with the Access Developer's Edition. In Access 2003, you can get it in the Office Developer Extensions
(http://msdn.microsoft.com/en-us/office/aa905403.aspx). In Access 2007, you can download the 2007 Developer Extensions and Runtime (http://office.microsoft.com/en-us/access/HA102188681033.aspx). To run the Wizard, go to the Visual Basic Window, choose Tools, select the wizard, and follow the directions.

There are a few things to keep in mind when creating an application that will be used with the Run-time:

1. NEVER use macros. If the macros fail, you don't get the HALT screen, the whole app just crashes.
2. Error trap EVERYTHING. You want a graceful exit from every error. This is good practice regardless.
3. Don't rely on the native Access toolbars or menus. You must provide all functionality yourself. For instance, if you rely on the Find button (binoculars) to search for a record, forget it. This button won't be available to the app when using the Run-time.
4. The install disk created with the wizard will be very much larger than your program. However, in this day of CD burners, this is not the issue it once was.

Now, one thing you can do to keep your users from modifying the application is to convert it to an MDE. This is not an executable either, but it DOES have all of the source code removed. That means the user cannot open a form, report, module, or macro in Design view. Tables and queries can still be modified though.

So between converting the app to an MDE and deploying it with the Access Run-time, this comes very close to the functionality of the EXE you are looking for.