CASE problem

  • Thread starter Thread starter AkAlan via AccessMonster.com
  • Start date Start date
A

AkAlan via AccessMonster.com

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.
 
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.
 
Back
Top