Using dates in IIF expression
-
Hi,
I have a datetime field which I am trying to use in my IIF expression statement.
I want to say: IIF (my datetime field = null, count(myotherfield), 0)
So I want to enable the 'true' condition in my IIF if the datetime field is blank and contains nothing in it.
So far I have tried:-
=iif(Fields!CLSDDATE.Value = 0, Count(Fields!OPENDATE.Value), 0)
=iif(Fields!CLSDDATE.Value = NULL, Count(Fields!OPENDATE.Value), 0)
=iif(Fields!CLSDDATE.Value = SystemDB.NULL, Count(Fields!OPENDATE.Value), 0)
(CLSDDATE is closed_date).
But none of these work.
-
Hi Zee,
Use this code. Hope this will solve your problem
=IIF(IsNothing(Fields!CLSDDATE.Value),"True","False")
Still if you have a problem pls feel free to revert back
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Please feel free to let me know if you are not clear or I’ve misunderstood anything.Thanks,
Arunkumar S P
-
Hi,
Thanks for that.
The problem is that it's not blank, it has the word 'NULL' in the datetime field if nothing is filled in.
How could I compare it to the text NULL instead?
It doesn't like it when you compare a datetime field to a string field and this is the problem.
-
IF it has the text, NULL, then you would need to do string comparison:
IIF(Fields!CLSDDATE.Value = "NULL", Count(Fields!OPENDATE.Value), 0)the only thing I did is add quotes (") around the word NULL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Ok I think I may need to make my IIF more specific as it seems like it's not showing the correct numbers it should (although it's got rid of the errors, thanks to you both for that)
Along with my IIF how could I also put additional conditions to match before the result is reached?
So for example I could use a condition where opendate is greater than or equal to GETDATE(-30) and opendate is less than or equal to GETDATE.
So I want the results for the past 30 days where the open date is between now and 30 days past.
how could I add this to my IIF?
-
I'm not clear in your requirement. Will u pls expalin me clearly?
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Please feel free to let me know if you are not clear or I’ve misunderstood anything.Thanks,
Arunkumar S P -
Hi,
sorry I'm just trying to pinpoint the IIF expression down to the correct results I need it to bring me back as the figures it's showing me are not correct (although the report doesn't error)
This IIF statement is not working:-
=IIF(Fields!CLSDDATE.Value = "NULL", Count(Fields!OPENDATE.Value), "hello")
and is returning my false state all over my report (which is hello).
If I remove the double quotation marks from the word NULL it errors.
If I use IsNothing it's not bringing back the correct figures.
How can I add another condition in there alongside the CLSDDATE one?
-
Try this one
=IIF(IsNothing(Fields!CLSDDATE.Value), Count(Fields!OPENDATE.Value), 0)
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Please feel free to let me know if you are not clear or I’ve misunderstood anything.Thanks,
Arunkumar S P -
Ok I have found the problem.
When I run the SQL directly against my database I can use IS NULL like this:-
WHERE CLSDDATE IS NULL
and this works.
However when I try to do this in my expression without any quotation marks it errors.
With quotation marks it gives me the false part of the expression every time without returning anything.
-
Based on your most recent post, the IsNothing function should be what you need as in VB.NET a database NULL usually produces an "Empty" object which evaluates to NOTHING. You can also try this:
=iif(Fields!CLSDDATE.Value = System.DBNULL.Value, Count(Fields!OPENDATE.Value), 0)I just corrected a typo, from SystemDB.Null to System.DBNULL, and added the .Value as System.DBNULL is an object/type, not a value so you need the .Value to do comparison.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Hi
Sorry, I'm still getting an error.
-
And the error would be?
Can you post the DDL, DML, and some test data like mentioned in my signature line?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
When I run the IsNothing with my expression it runs fine but the figures are not correct.
The figures are showing 24 helpdesk jobs still open whereas it should be 14. I have counted manually from my table.
I have also run an SQL query directly against the database and it has returned 14 rows. In my SQL Query I put:-
WHERE CLSDDATE IS NULL
So why 'IS NULL' would work in my SQL Query and not in my expression is strange.
I have also tried to put as my expression:-
=iif(Fields!CLSDDATE.Value = System.DBNULL.Value, Count(Fields!OPENDATE.Value), 0)
The word 'Value' after DBNULL has a red line underneath it as it doesn't like it. 'Value' is also not in the intellisense drop down box.
When I preview it I am getting the following error:-
An error occurred during local report processing
The definition of the report 'report name' is invalid.
The value expression for the textbox 'textbox22' contains an error 'BC30452' Operator '=' is not defined for types 'Object' and 'System.DBNull'
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply