>>>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!
>
>It is good that you posted this message. It looks like different functions have different degree of tolerance <g> E.g. STR doesn't work, but VAL does and other examples.

Thanks.

It also seems to matter where the NVL() is placed. For example

STR(NVL(myVar,0),7,2)

avoids these problems when compared to

NVL(STR(myVar,7,2),"   0.00")

as well as being easier to maintain.