IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, email@example.com, November 1999.
Learning to handle Nulls can be frustrating. Occasionally I hear newbies ask, "How can I prevent them?" Nulls are a very important part of your database, and it is essential that you learn to handle them.
A Null is "no entry" in a field. The alternative is to require an entry in every field of every record! You turn up at a hospital too badly hurt to give your birth date, and they won't let you in because the admissions database can't leave the field null? Since some fields must be optional, so you must learn to handle nulls.
Nulls are not a problem invented by Microsoft Access. They are a very important part of relational database theory and practice, part of any reasonable database. Ultimately you will come to see the Null as your friend.
Think of Null as meaning Unknown.
Open the Immediate Window (press Ctrl+G), and enter:
? Null = 0
VBA responds, Null. In plain English, you asked VBA, Is an Unknown equal to Zero?, and VBA responded with, I don't know. Null is not the same as zero.
If an expression contains a Null, the result is often Null. Try:
? 4 + Null
VBA responds with Null, i.e. The result is Unknown. The technical name for this domino effect is Null propagation.
Nulls are treated differently from zeros when you count or average a field. Picture a table with an Amount field and these values in its 3 records:
4, 5, Null
In the Immediate window, enter:
? DCount("Amount", "MyTable")
VBA responds with 2. Although there are three records, there are only two known values to report. Similarly, if you ask:
? DAvg("Amount", "MyTable")
VBA responds with 4.5, not 3. Nulls are excluded from operations such as sum, count, and average.
Hint: To count all records, use Count("*") rather than Count("[SomeField]"). That way Access can respond with the record count rather than wasting time checking if there are nulls to exclude.
VBA uses quote marks that open and immediately close again to represent a string with nothing in it. If you have no middle name, it could be represented as a zero-length string. That is not the same as saying your middle name is unknown (Null). To demonstrate the difference, enter this into the Immediate window:
? Len(""), Len(Null)
VBA responds that the length of the first string is zero, but the length of the unknown is unknown (Null).
Text fields in an Access table can contain a zero-length string to distinguish Unknown from Non-existent. By default the Allow Zero Length property is set to No, as the difference between a Null and a zero-length string is not very apparent to the user and the interface can be quite confusing. For data stored within an Access database, this property should rarely be changed to Yes.
These are terms that sound similar but mean do not mean the same as Null, the unknown value.
VBA uses Nothing to refer to an unassigned object, such as a recordset that has been declared but not set.
VBA uses Missing to refer to an optional parameter of a procedure.
To help you avoid common traps in handling nulls, see: Common Errors with Null
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Nulls: Do I need them?'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...