>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.

I came across this situation while testing a NVL() function.

myVar = .NULL.
? NVL(PADL(ALLTRIM(STR(myVar)),5,'0'),'00000') ---> Function argument invalid error

When I rewrote it to

? PADL(NVL(myVar,0),5,'0')

it worked as expected. Strangely enough, though,

? NVL(PADL(myVar,5,'0'),'00000')

also worked since PADL() appears more forgiving than STR()

myVar = .NULL.
? STR(myVar) ---> error
? PADL(myVar,5,'0') ---> .NULL.

The actual code I was testing would not have run across this issue since myVar was really myField. I couldn't plug a NULL into the table because the field did not accept nulls. However, a query using an OUTER JOIN could have resulted in nulls and it seemed easier to just use a variable than recrete the whole scenerio. It wasn't easier.......but I did learn something!