CASE problem "AkAlan via AccessMonster.com" <u18147[ at ]uwe> 02.03.2006 19:54:17 I had the following IIF function as the recordsource of a text box in an Access mdb and it worked fine. =IIf(IsNull([STATUS_STOP_TIME]),DateDiff("n",Now(),([STATUS_START_DATE]+ [STATUS_START_TIME]))/60,DateDiff("n",([STATUS_START_DATE]+[STATUS_START_TIME] ),([STATUS_STOP_DATE]+[STATUS_STOP_TIME]))/60) Simply put, If the stop time is null, use the difference between the start and NOW() otherwise use the existing start and stop values. Now have converted to a project and would like this function to be a column in the stored procedure I'm using as a recordsource for a form. I know it needs to be a CASE function but I can't seem to get it to work. I tried to start simple with this: CASE WHERE [STATUS_STOP_DATE] = null THEN 1 ELSE 0 END It never returns a 1 when the stop date is null. I think I could work this out if I could get past determining null. Any help is appreciated. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200603/1

Re: CASE problem "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 02.03.2006 20:09:52 With the default ANSI setting, you cannot make a direct comparaison with the NULL value. You must either use the IS NULL operator or the IsNull (Value, New_Value_if_Null) or the Coalesce() functions. With the IsNull() function, take care: it's not a logical function returning TRUE or FALSE but a function that will return Value if not null or the new_value if null. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "AkAlan via AccessMonster.com" <u18147[ at ]uwe> wrote in message news:5caad49f849c2[ at ]uwe... [Quoted Text] >I had the following IIF function as the recordsource of a text box in an > Access mdb and it worked fine. > > =IIf(IsNull([STATUS_STOP_TIME]),DateDiff("n",Now(),([STATUS_START_DATE]+ > [STATUS_START_TIME]))/60,DateDiff("n",([STATUS_START_DATE]+[STATUS_START_TIME] > ),([STATUS_STOP_DATE]+[STATUS_STOP_TIME]))/60) > > Simply put, If the stop time is null, use the difference between the start > and NOW() otherwise use the existing start and stop values. > > Now have converted to a project and would like this function to be a > column > in the stored procedure I'm using as a recordsource for a form. I know it > needs to be a CASE function but I can't seem to get it to work. I tried to > start simple with this: > CASE WHERE [STATUS_STOP_DATE] = null THEN 1 ELSE 0 END > It never returns a 1 when the stop date is null. I think I could work this > out if I could get past determining null. Any help is appreciated. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-adp-sqlserver/200603/1