473,585 Members | 2,547 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

What is Null?

ADezii
8,834 Recognized Expert Expert
Null as it relates to database development is one of life's little mysteries and a topic of total confusion for novices who venture out into the database world. A Null Value is not zero (0), a zero (0) length string, an empty Field, or no value at all - so exactly what is Null? The purpose of this Topic is hopefully to explain what a Null Value is, discuss some peculiarities about Nulls, show how we can detect them, and finally, how to convert them into not-so-devious entities.
  1. What is a Null Value?
    1. Null is a Variant subtype used to indicate that a data item contains no valid data, is missing data, or the data is simple unknown. This special value is not a real value like "Smith", #12/24/2006#, 25.98, etc. but is an indicator that data is missing, unknown, or not applicable. The data in a Table or Query Field, as well as in a Form or Report Control has the Variant Data Type by default. When you leave a Field or Control blank, Null is stored automatically. One critical point to remember, and one worth restating, is that only the Variant Data Type con contain Nulls.
  2. Some peculiarities concerning Null Values.
    1. If 'any' part of an Expressions evaluates to the Null Value, the entire Expression also has the Null Value. This is called propagation of Null Values and is a critical reason why we should take steps to handle them.
    2. If the Argument of a built-in or custom Function evaluates to Null, the Function usually returns a Null Value. For example, if you use SQL or an Aggregate Function to calculate a summary value of a Field for a group of Records, Records with Null Values in the Field won't be counted.
    3. When you join Tables in a Query, Records with Null Values in the join Field are not included in the Query result.
    4. When you create a Relationship and enforce Referential Integrity, you can still create Orphans in the Child Table by leaving the join Field blank in the Child Table.
  3. How can you detect Null Values?
    1. Fortunately, we have a Function that returns a Boolean value indicating whether or not an expression contains valid data (Null). This Function is the IsNull() Function, and consists of only 1 required Argument:
    Expand|Select|Wrap|Line Numbers
    1. IsNull(<expression>)      'returns True if expression is Null
    2. 'The required expression argument is a Variant containing a numeric expression or string expression.
    3.  
  4. Converting Nulls to something less harmful.
    1. Fortunately, we have an ally in the never ending fight against Nulls, and this is the Nz() Function. You can use this Function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

      Expand|Select|Wrap|Line Numbers
      1. Nz(variant, [valueifnull])
      The variant Argument is a variable of data type Variant. The valueifnull Argument is Optional (unless used in a query). It is a Variant that supplies a value to be returned if the variant argument is Null. This argument enables you to return a value other than zero or a zero-length string.

      If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the NZ function should always include the valueifnull argument,

      If the value of variant isn't Null, then the Nz function returns the value of variant.
    Expand|Select|Wrap|Line Numbers
    1. Dim varVariant As Variant
    2. varVariant = Null
    3.  
    4. Debug.Print Nz(varVariant)              'returns a Zero length String
    5. Debug.Print Nz(varVariant, "Null")      'returns the String Null
    6.  
    7. Dim intTest As Integer
    8. intTest = 600
    9. Debug.Print Nz((intTest * varVariant) / 16, 9999)      'returns 9999
Oct 1 '07 #1
3 12270
nico5038
3,080 Recognized Expert Specialist
Although the Null propagation can cause havoc, it's also an opportunity to save on coding.

The regular occurring problem of having a person's name "in parts" and wanting it to concatenate into a readable string often leaves us with an additional space like:
Expand|Select|Wrap|Line Numbers
  1. Select [Lastname] & ", " & [MiddleInitial] & " " & [firstname] as Fullname, ...
  2.  
will have two spaces when there's no [MiddleInitial].
Often this is solved with:
Expand|Select|Wrap|Line Numbers
  1. Select [Lastname] & ", " & IF(IsNull([MiddleInitial]),"",[MiddleInitial] & " ") & [firstname] as Fullname, ...
  2.  
But using the Null propagation and the plus ("+") sign we can use:
Expand|Select|Wrap|Line Numbers
  1. Select [Lastname] & ", " & [MiddleInitial] + " " & [firstname] as Fullname, ...
  2.  
Here the "+" will cause Null propagation and thus the trailing space won't show.

Nic;o)
Oct 1 '07 #2
ADezii
8,834 Recognized Expert Expert
Although the Null propagation can cause havoc, it's also an opportunity to save on coding.

The regular occurring problem of having a person's name "in parts" and wanting it to concatenate into a readable string often leaves us with an additional space like:
Expand|Select|Wrap|Line Numbers
  1. Select [Lastname] & ", " & [MiddleInitial] & " " & [firstname] as Fullname, ...
  2.  
will have two spaces when there's no [MiddleInitial].
Often this is solved with:
Expand|Select|Wrap|Line Numbers
  1. Select [Lastname] & ", " & IF(IsNull([MiddleInitial]),"",[MiddleInitial] & " ") & [firstname] as Fullname, ...
  2.  
But using the Null propagation and the plus ("+") sign we can use:
Expand|Select|Wrap|Line Numbers
  1. Select [Lastname] & ", " & [MiddleInitial] + " " & [firstname] as Fullname, ...
  2.  
Here the "+" will cause Null propagation and thus the trailing space won't show.

Nic;o)
Excellant point Nic;o, unfortunately this may be the only case where Null Propagation would be an Asset rather than a Liability. Thanks for the Tip, it's definitely work keeping in mind.
Oct 1 '07 #3
NeoPa
32,566 Recognized Expert Moderator MVP
ADezii:
Excellent point Nico, unfortunately this may be the only case where Null Propagation would be an Asset rather than a Liability.
I would have to disagree there my friend.

The Null is a tremendously useful animal to have available. There are many situations where it is important to differentiate between a value (of whatever type) and no value at all (or Null). When usage of the Null is properly understood it can be very beneficial. Clearly it's confusing to novices and that's perfectly understandable, but it's not useless merely because it's hard to understand.

Many things can be handled more easily by simply choosing a specific value to convert it to when it does occur certainly, but that's not the whole story. Indeed, I use that technique myself quite heavily, but it's not for all situations.
Jun 14 '11 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

0
6799
by: Ravi Tallury | last post by:
Hi We are having issues with our application, certain portions of it stop responding while the rest of the application is fine. I am attaching the Java Core dump. If someone can let me know what the issue is. Thanks Ravi
4
2639
by: MAB | last post by:
I have two tables in SQL 6.5 database with identical fields and indexes. One contains the data of August 2003 and other July 2003. Now the august table is larger ( about 40000 more rows ) than the july table but i've noticed that the same queries perform much faster on the august table than the july table. Ive tried this with many different...
2
6892
by: Eddie | last post by:
When I subtract dates, I can't figure out what I get out. I first I thought it was in seconds, but that's not it. Then I figured maybe HHMMSS, but that does not seem to be it too. How can I subtract two dates and get the resualt in HH:MM:SS format? Thanks for any help. -Eddie
3
2193
by: subnet | last post by:
I'm trying to write a very simple program that uses a signal-based synchronization between two processes. Here's it: ----------------------------------------------- /* The world's simplest syncronization example */ #include <stdio.h> #include <stdlib.h> #include <signal.h> #include <unistd.h> void handler(int sig);
26
4445
by: Lasse Edsvik | last post by:
Hello I'm trying to build a simple COM+ app in vs.net using C# and i cant register it in component manager..... what more is needed than this: using System; using System.EnterpriseServices;
13
5029
by: Jason Huang | last post by:
Hi, Would someone explain the following coding more detail for me? What's the ( ) for? CurrentText = (TextBox)e.Item.Cells.Controls; Thanks. Jason
16
10364
by: Abhishek | last post by:
why do I see that in most C programs, pointers in functions are accepted as: int func(int i,(void *)p) where p is a pointer or an address which is passed from the place where it is called. what do you mean by pointing to a void and why is it done? Aso, what happens when we typecast a pointer to another type. say for example int *i=(char...
37
5435
by: Egbert Nierop \(MVP for IIS\) | last post by:
In win32 mode, a BSTR was UINT length prefixed and terminated exactly by a zero char. So if you allocated "Hello World" that would allocate 28 bytes. In x64 and (IA64 as well) it would become 32 bytes, because of the fact that a pointer, is 8 bytes instead of 4 bytes. The length prefix -still- is a UINT however and not a UINT_PTR.
9
2266
by: rhaazy | last post by:
Using MS SQL 2000 I have a stored procedure that processes an XML file generated from an Audit program. The XML looks somewhat like this: <ComputerScan> <scanheader> <ScanDate>somedate&time</ScanDate>
0
7835
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8334
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6596
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5707
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5386
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3831
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3856
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1444
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1171
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.