Check for Null in Expression

1) I need to know how to check for the null value. IsNothing and IS
System.DBNull.Value and Len(Fields!mmActDate.Value)=0 doesn't seem to work.
=IIf( Fields!mmActDate.Value IS System.DBNull.Value, MonthName(
Fields!mmPlanDate.Value ),MonthName( Fields!mmActDate.Value ))
I get a warning message for this,
The value expression for the textbox â'mmActDateâ' contains an error: Argument
'Month' is not a valid value.
2) Can someone give some examples for Switch and choose Statements to use in
Expression like IIf. thanks.
thanks in advance.Use
Fields!mmActDate.Value Is Nothing
--
Thanks,
ID
"Giya" wrote:
> 1) I need to know how to check for the null value. IsNothing and IS
> System.DBNull.Value and Len(Fields!mmActDate.Value)=0 doesn't seem to work.
> =IIf( Fields!mmActDate.Value IS System.DBNull.Value, MonthName(
> Fields!mmPlanDate.Value ),MonthName( Fields!mmActDate.Value ))
> I get a warning message for this,
> The value expression for the textbox â'mmActDateâ' contains an error: Argument
> 'Month' is not a valid value.
> 2) Can someone give some examples for Switch and choose Statements to use in
> Expression like IIf. thanks.
> thanks in advance.
>|||Thanks for the response. I tried the one below, But still i get same error.
=IIf(Fields!mmActDate.Value Is Nothing,
MonthName( Fields!mmPlanDate.Value),
MonthName( Fields!mmActDate.Value ))
:(
"exkievan" wrote:
> Use
> Fields!mmActDate.Value Is Nothing
> --
> Thanks,
> ID
>
> "Giya" wrote:
> > 1) I need to know how to check for the null value. IsNothing and IS
> > System.DBNull.Value and Len(Fields!mmActDate.Value)=0 doesn't seem to work.
> >
> > =IIf( Fields!mmActDate.Value IS System.DBNull.Value, MonthName(
> > Fields!mmPlanDate.Value ),MonthName( Fields!mmActDate.Value ))
> >
> > I get a warning message for this,
> >
> > The value expression for the textbox â'mmActDateâ' contains an error: Argument
> > 'Month' is not a valid value.
> >
> > 2) Can someone give some examples for Switch and choose Statements to use in
> > Expression like IIf. thanks.
> >
> > thanks in advance.
> >|||The IsNothing function can be used in an IIF statement like this:
=IIF(IsNothing(Fields!your_field.value), Action_if_null, Action_if_not_null)
So, in your case, this should work:
=IIf( IsNothing(Fields!mmActDate.Value), MonthName(
Fields!mmPlanDate.Value ),MonthName( Fields!mmActDate.Value ))
Regards.
--
Please mark the correct/helpful answers!|||Can you describe the next properties? Maybe the problem is not the IsNothing
function.
Name
Value
Format|||Yes, thats right. The problem is not in the Is Nothing function. It is in the
MonthName function. thanks.
"F. Dwarf [MCP]" wrote:
> Can you describe the next properties? Maybe the problem is not the IsNothing
> function.
> Name
> Value
> Format|||Try this:
=MonthName(iif(IsNothing(Fields!mmActDate.Value), Fields!mmPlanDate.Value,
Fields!mmActDate.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Giya" <Giya@.discussions.microsoft.com> wrote in message
news:AAA118AF-E0F4-45F7-A0B6-749CB5A106E9@.microsoft.com...
> Yes, thats right. The problem is not in the Is Nothing function. It is in
> the
> MonthName function. thanks.
> "F. Dwarf [MCP]" wrote:
>> Can you describe the next properties? Maybe the problem is not the
>> IsNothing
>> function.
>> Name
>> Value
>> Format|||Try this instead:
=MonthName(iif(IsNothing(Fields!mmActDate.Value), Fields!mmPlanDate.Value,
Fields!mmActDate.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Giya" <Giya@.discussions.microsoft.com> wrote in message
news:0CB6BF4F-4FEE-49B0-83E1-35FAB145A4B8@.microsoft.com...
> Thanks for the response. I tried the one below, But still i get same
> error.
> =IIf(Fields!mmActDate.Value Is Nothing,
> MonthName( Fields!mmPlanDate.Value),
> MonthName( Fields!mmActDate.Value ))
> :(
> "exkievan" wrote:
>> Use
>> Fields!mmActDate.Value Is Nothing
>> --
>> Thanks,
>> ID
>>
>> "Giya" wrote:
>> > 1) I need to know how to check for the null value. IsNothing and IS
>> > System.DBNull.Value and Len(Fields!mmActDate.Value)=0 doesn't seem to
>> > work.
>> >
>> > =IIf( Fields!mmActDate.Value IS System.DBNull.Value, MonthName(
>> > Fields!mmPlanDate.Value ),MonthName( Fields!mmActDate.Value ))
>> >
>> > I get a warning message for this,
>> >
>> > The value expression for the textbox 'mmActDate' contains an error:
>> > Argument
>> > 'Month' is not a valid value.
>> >
>> > 2) Can someone give some examples for Switch and choose Statements to
>> > use in
>> > Expression like IIf. thanks.
>> >
>> > thanks in advance.
>> >

Check for NULL in CASE

SELECT whatever_field,
CASE LEN(DrAccount)
WHEN 12 THEN DrAccount
ELSE CASE (Note1)
WHEN NULL THEN Location + DrAccount
ELSE Note1 + DrAccount
END
END AS Account
FROM Table1

The purpose of the CASE(Note1) is when Note1 column is null, return Location+DrAccount.

The actual result is when Note1 column is null, it always returns null, Location+DrAccount is not executed. When Note1 column is not null, it returns correctly Note1+DrAccount.

The problem seems to reside in validating the null value in
WHEN NULL

How to check for null in CASE(fieldname) WHEN ?

Have you considered using Coalesce? Coalesce(Note1 + DrAccount, Location + DrAccount)

COALESCE

Returns the first nonnull expression among its arguments.

Syntax

COALESCE(expression [,...n])