IIF problem

Hi all,
I have a problem with an IIF expression on a cell of my report (SSRS 2005).
I have a query that returns some datafields bigint that represent a date.
Because some values are null, I put this expression in my cell:
=IIF(Fields!LedgerReferenceDateIdMinus1.Value="","is null","is not null")
but on report rendering, I get this error:
#Error
in the case of not null.
What have I made wrong?
Thanks a lot.
LuigiOn Apr 14, 8:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
> Hi all,
> I have a problem with an IIF expression on a cell of my report (SSRS 2005).
> I have a query that returns some datafields bigint that represent a date.
It looks like you are getting your SQL syntax and your Expression
syntax mixed up when looking for Null values.
When using an expression for an RS field, you can check if a value is
null using the IsNull function, like so:
=IIF(IsNull(Fields!LedgerReferenceDateIDMinus1.value), '',
Fields!LedgerReferenceDateIDMinus1.value)
Or, you could check for Null values within your SQL query which is
probably better, because then you never have to deal with Null values
within that field once the dataset reaches your report:
CASE WHEN LedgerReferenceDateIDMinus1 IS NULL THEN '' ELSE
LedgerReferenceDateIDMinus1 END as LedgerReferenceDateIDMinus1
Good luck!
<
> =IIF(Fields!LedgerReferenceDateIdMinus1.Value="","is null","is not null")
> but on report rendering, I get this error:
> #Error
> in the case of not null.
> What have I made wrong?
> Thanks a lot.
> Luigi|||"Jerry H." wrote:
> When using an expression for an RS field, you can check if a value is
> null using the IsNull function, like so:
>
> =IIF(IsNull(Fields!LedgerReferenceDateIDMinus1.value), '',
> Fields!LedgerReferenceDateIDMinus1.value)
>
> Or, you could check for Null values within your SQL query which is
> probably better, because then you never have to deal with Null values
> within that field once the dataset reaches your report:
>
> CASE WHEN LedgerReferenceDateIDMinus1 IS NULL THEN '' ELSE
> LedgerReferenceDateIDMinus1 END as LedgerReferenceDateIDMinus1
>
> Good luck!
Hi Jerry, I'll try with IsNull in the report.
Thanks a lot for your detailed answer.
Luigi|||I slightly problem.
IfNull give me the "Unrecognized identifier" error.
This is my expression:
=IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))|||Move your first closing parentheses so that it is between the "e" in
Value and the first comma in your expression.
At the moment, you are passing two parameters over to IsNull, which
only takes one parameter.
On Apr 14, 9:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
> I slightly problem.
> IfNull give me the "Unrecognized identifier" error.
> This is my expression:
> =IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))|||"Jerry H." <boilersrock@.gmail.com> wrote in message
news:7edfeec7-a2fe-4d50-8cef-503e5798b1b5@.f36g2000hsa.googlegroups.com...
> Move your first closing parentheses so that it is between the "e" in
> Value and the first comma in your expression.
> At the moment, you are passing two parameters over to IsNull, which
> only takes one parameter.
> On Apr 14, 9:39 am, Luigi <ciupazNoSpamGra...@.inwind.it> wrote:
>> I slightly problem.
>> IfNull give me the "Unrecognized identifier" error.
>> This is my expression:
>> =IIF(IsNull(Fields!GrossDeltaMinus1.Value,''),FormatNumber(Fields!GrossDeltaMinus1,2))
>
I had that same problem, "Unrecognized identifier" error. and found
IsNothing worked instead.