> My personal preference is to fix IFNULL so it will > always work. The problem is that with ZERO set ON, every time an expression is encountered in code, NULL values of any numeric type are immediately converted to 0. So, if you are going to fix IFNULL, what behavior would you prefer to see?
For instance: SET ZERO ON SET VAR vNullInt INT = NULL and then: (IFNULL(.vNullInt, 'Null', 'Non-null')) should probably return 'Null'. However: (IFNULL(.vNullInt*10, 'Null', 'Non-null')) should return which one? I would say, probably 'Non-null', since the expression would certainly return a non-null value if used in code. And how about: (IFNULL((.vNullInt), 'Null', 'Non-null')) Does the presence of the parens around the .vNullInt turn the reference to a NULL value into an expression which should respect SET ZERO ON? In other words, it's not immediately clear to me how the IFNULL function could ever "see" a NULL value in its first parameter if ZERO is ON. One solution would be the addition of another function IFNULLVAR which would accept a Variable Name (as Text) and two arguments, and test the variable with that name for NULL. This would allow you to get around the expression evaluation. It wouldn't help you in your form problem, since the lookup would still be evaluated and converted to 0. A work around might be changing your columns to default to zero and then not allowing NULLs. But I'm guessing that your REALLY do need to differentiate between the NULL values and the really 0 ones. A final work-around would be as follows: Keep your tables as-is. Set ZERO OFF. Declare a view for each table that contains number values with all the real columns and additional values for each number-type field of the form: CalcCol = (IFNULL(RealCol, 0, RealCol)) Base all your work on the single-table views (which should be editable). For forms and editing use the RealCol and for reports and calculations use the CalcCol. For instance, if you have a table: CREATE TABLE RawTestTable ( + IndivID INTEGER, + MinBalance REAL, + AverageRate REAL ) then create a view: CREATE VIEW TestTable ( + IndivID, + MinBalance, + CalcMinBalance, + AverageRate, + CalcAverageRate ) AS + SELECT + IndivID, + MinBalance, + (IFNULL(MinBalance, 0, MinBalance)), + AverageRate, + (IFNULL(AverageRate, 0, AverageRate)) + FROM RawTestTable -- Larry __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
