Larry I can only tell you my solution to this long standing dilemma
1. I keep NULL set to -0- ALL the time, EXCEPT when editing on a form. In 7.1, with menus serving as the form, all code called by the form in the way of custom eeps or eeps SET NULL -0- at the start and back to ' ' at the end. 2. I ALWAYS keep EQNULL OFF 3. I ALWAYS build my where clauses to account for possible NULL values: instead of IF var1 <> .var2 THEN I'll use IF var1 <> .var2 OR (var1 is NOT NULL and var2 IS NULL) OR (var1 IS NULL and var2 IS NOT NULL) THEN A little longer to write, but no doubts that way. David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Lawrence Lustig" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Tuesday, December 28, 2004 11:45 AM Subject: [RBG7-L] - NULL comparisons (was re: Form Expression) > > When comparing text columns to text variables you can actually > > get away with "column = .var" when EQNULL is ON, but when using > > non-text columns like INTEGERs, R:BASE is not as forgiving. If > > you are specifically looking for NULL values you should use a > > where clause with "column IS NULL". If you are using an INTEGER > > variable which might be NULL, then you need to put parentheses > > around the variable like "column = (.var)". > > I've been struggling with this issue myself, and it seems to me the following > is true: > > If you want to compare an INTEGER column to a variable that may or may not > contain NULL, there is no way to do so with NULL set to ' '. > > 1. If EQNULL is set to OFF, the comparison is not valid, as you would expect. > > 2. If EQNULL is set ON and you use IntegerCol = .vMaybeNullVariable then the > variable is converted to a blank and you get an error message about an invalid > right side of the comparison. > > 3. If EQNULL is set ON and you use IntegerCol = (.vMaybeNullVariable) then the > variable is converted to 0 (because the parens turn it into an expression, and > because of the EQNULL setting the NULL is treated as a 0). > > Of these, the one I expect to work is #2, since you have a plain, > non-expression variable containing NULL and an EQNULL setting of ON. It seems > odd to me that R:Base is treating it like a blank space (the behavior you would > expect with &vMaybeNullVariable rather than .vMaybeNullVariable). > > So it seems to me that you need to do one of two things: > > 1. Set NULL to '-0-' or some other value before executing the command (not a > great option if the command is BROWSE or PRINT and you prefer the NULLs to > appear as blanks). > > 2. Add program code to reconfigure the WHERE clause by looking at each possible > column comparison, checking for NULL, and building a different WHERE clause > with IS NULL comparisons. > > I'm really hoping for some way to do reliable NULL comparisons using EQUALS > with NULL set to ' ', so if someone can explain how to handle it, I would be > most appreciative. > -- > Larry > >
