Have you tried using Birth1day itself instead of -0- et alia? Bill Cook Kent WA USA ----- Original Message ----- From: "Alastair Burr" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 16, 2001 3:06 AM Subject: re: Date fields in forms & ifnull return null questions
> Many thanks for everyone's replies. > > A number of points have been raised which I'll try to address: > > The settings of ZERO and EQNULL had no effect in any combination of off or > on - except that I got different error message - but I always got a zero in > the result and never a null. > > Bernie, you said it worked for you so could you please show me the syntax > you used for the second argument. I've tried both -0- and null both enclosed > in quotes and not. > > In the process of checking these alternatives I also got strange messages > about not being able to open the designer while a form is running - when > there was no form running and also about not having enough dynamic space to > process the expressions. Eventually R:Base hung (rather than crashing). > > I'm using v6.5++ Windows (32-bit) U.S. Version, Build: 1.84xRT03 of Win98SE. > > Here's the full expression copied and pasted from the form: > > v1AgeNext = (ifnull(Birth1day, 0, ( (INT( (.#Date - Birth1day) / 365 )) + > ) )) > > v1AgeNext is defined as an integer and the column Birth1day is defined as a > date. > Arg2 is currently set to zero so that I get no errors. This works but, > naturally, returns 0 when Birth1Day is null. > > All these fail: > v1AgeNext = (ifnull(Birth1day, -0-, ( (INT( (.#Date - Birth1day) / 365 )) + > 1) )) > v1AgeNext = (ifnull(Birth1day, '-0-', ( (INT( (.#Date - Birth1day) / 365 )) > + 1) )) > v1AgeNext = (ifnull(Birth1day, null, ( (INT( (.#Date - Birth1day) / 365 )) + > 1) )) > v1AgeNext = (ifnull(Birth1day, 'null', ( (INT( (.#Date - Birth1day) / 365 )) > + 1) )) > v1AgeNext = (ifnull(Birth1day, , ( (INT( (.#Date - Birth1day) / 365 )) + > ) )) > > > Razzak, I happened to be using Sans Serif, Regular, size 7 so I changed the > size to 8 and still get the same effect in my database. So I went to ConComp > and tried there and got the same thing. I've taken an image of the result > which I will send to you privately rather than fill the list-user's e-mail > up with a big file. All I changed was the date format and extended the date > fields on the form. > > Regards, Alastair. > > > > A D B Burr, > St. Albans, UK >
