Why do variables directly assigned .NULL. not seem to work correctly while fields and SCATTERed variables give the expected results?

What are the states in which a value can exist? In Visual FoxPro there are now three conditions, the value can be equal to something (.T.) not equal to something (.F.) or be in a state where its value cannot be determined (NULL). Unless you specifically test for a NULL condition, the answer to any ‘question’ involving a NULL value is always NULL Consider the following:

luVal = NULL
? luVal = 0
? luVal = "A CHARACTER STRING"
? luVal = DATE()
? luVal = .T.

The value returned in every case is actually NULL An exception to this rule is provided by the TYPE() function which invariably returns a type of “L” if the value was originally defined as NULL (as in this example:

? TYPE("luVal") && Returns ‘L’

However if a value was defined as some other data type and has subsequently acquired a NULL value the TYPE() function returns the original data type - with potentially catastrophic results!

lnTotal = 100
luVal = 10
? TYPE( 'luVal' ) && Type = “N”
luVal = IIF( luVal = 20, luVal, NULL )
? TYPE( 'luVal' ) && Value = NULL but Type = “N”
lnTotal = lnTotal + luVal && lnTotal = NULL - not even 100!!!

Why is this so bad? Consider what would happen if you were to be using code which accumulated a values inside a loop (e.g. a SCAN) and one record contained a NULL Not only would your total so far be lost, but you would not even get the value back because as soon as you reached the null record the value in the accumulator would be set to NULL and all subsequent additions would simply end up as NULL You would not get an error, but you certainly would not get the result you desired.

Fortunately you CAN use functions like SUM() and the CALCULATE functions, or SQL SUM() even when NULL values are present. Visual FoxPro is intelligent enough to simply ignore such values - and indeed this is one of positive benefits of NULL support when calculating averages and counting records - NULLS are ignored and do not affect the results.
One other specific gotcha! with NULL values arises when you are concatenating character strings, one of which contains a NULL.

luVal = NULL
? 'Fred ' + luVal && Gives a “Data Type Mismatch” Error
? 'Fred ' + ALLTRIM( luVal ) && No error, but the result is now NULL!

Personally, I think that this one is a bug! If a data type mismatch arises when a character string containing a NULL is concatenated with another, then simply applying an ALLTRIM() function should not allow the operation to proceed without error. I fell over this one in an application and it took a lot of debugging to find out what was happening.
Does this mean you should not use NULLS - certainly not! However, I find it best to think of the NULL value as being translated as ‘I don’t know’. Any operation which encounters a NULL is going to end up not knowing what to do with it and simply setting the result to NULL. You must consider this possibility and cater for it properly in your code.

There are two functions, and only two, which can deal properly with NULL values. First, the ISNULL() which returns a logical value indicating whether the expression tested contains a NULL value. Second is NVL(), which acts like the immediate if (IIF) and substitutes the specified value if the one being tested is NULL. These should be a major part of your armory if you are dealing with any situation in which a NULL value could turn up.