>I'm doing some processing by converting data from Access tables and then inserting the results into an older VFP table using VFP 9. It seems that the SQL Select to get a cursor from the Access tables is now giving me nulls when the access table fields are empty.
>
>Now, when I try INSERTING into the VFP table I get the dreaded "Field
does not support Null values". And indeed the VFP table structure does *not* support Nulls.
>
>I tried adding a SET NULL OFF at the top of my program, but that doesn't appear to affect the outcome.
>
>Poking around the archives suggests that this is a new behavior that is designed to more closely comply with the ANSI SQL standard. All well and good but it will break YEARS of my code if I can't find a more elegant way of globally changing this. (Maybe go back to VFP 8.0)
>
>I was able to to the insert if I use something like:
>
>>INSERT INTO Person (ssn,LAST);
> VALUES (ISNULL(cR.ssn),"",cR.ssn,;
> (ISNULL(cR.last_name,"",cR.last_name)
>
>
>and I see that there is the NVL function which would probably substitute for the IIF thing...but gawd what a pain this would be.
>
>Also, I see that SET ENGINEBEHAVIOR to 80 or 70 does not appear to help.
>
>So, what are my options here?
Larry,
PMFJI but where did you read "the new behavior"? Set null off is still supported and works the way it should be. However in your code you're not doing something that would benefit from "set null off". ie: This one would benefit from "set null off"INSERT INTO Person (ssn) VALUES (nvl(cR.ssn,""))
Cetin