| Author |
Topic |
|
jhermiz
3564 Posts |
 Posted - 2004-11-15 : 15:27:06
|
I want to set a field like so:=IIf(Fields!PriorityID.Value="Low",IIf(IsNull(Fields!AICD.Value),DateDiff("d",Fields!AITD.Value,Now()),DateDiff("d",Fields!AITD.Value,Fields!AICD.Value)))That is if the priority id = low and the AICD is null do the first datediff else do the second date diff.But I cannot because RS doesn't know what the hell IsNull is.Is there any way to accomplish what I am trying to do here ?Thanks,Jon |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-15 : 15:30:35
|
| Here is what I have done for NULL data:= IIF(Len(Fields!DriverGroup.Value) < 1, "<No Driver Group>", Fields!DriverGroup.Value)Tara |
 |
|
|
jhermiz
3564 Posts |
Posted - 2004-11-15 : 15:37:24
|
Hmm good idea but Im getting an error:=IIf(Fields!PriorityID.Value="Low", IIf(Len(Fields!AICD.Value) < 1,DateDiff("d",Fields!AITD.Value,Now()),DateDiff("d",Fields!AITD.Value,Fields!AICD.Value)))\\jakah-iis-2\ims\ims-reports\Issue Action Item Priority Stats Summary.rdl The value expression for the textbox ‘textbox4’ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.I wonder if RS understands datediff ? |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-15 : 15:39:05
|
| on second thought my iif is wronglet me fix it with something... |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-15 : 15:47:07
|
Thanks T=IIf(Fields!PriorityID.Value="Low",IIf(Len(Fields!AICD.Value) < 1,DateDiff("d",Fields!AIOD.Value,Now()),DateDiff("d",Fields!AIOD.Value,Fields!AICD.Value)),0) |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-15 : 15:52:26
|
| tara one question...if i have that iff expression in say a details section and I want to sum it, it doesnt appear that I can do:=Sum(sumTextBoxInTheReport)Looks like it can only sum datasets ?Is this true ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-15 : 15:56:16
|
| I sum on fields quite a bit. Are you getting an error? None of the sums that I am doing also have the IIF though.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-15 : 15:58:39
|
| Well here is what I am trying to do...I get the number of days in the detail section of an overdue action: Which is that big iff...so it gives me some number of days (End-Start). Fine and dandy I even see it in the details section. Now I can easily hide this field so that all my end user would see is the sum at the bottom. So in the footer I tried =Sum(txtLowDays) where txtLowDays is the name of the textbox that contains that iif. But it complains that txtLowDays is not declared, which I assume its looking for it as a field in the db. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-15 : 16:01:27
|
| I don't think you can use the name of the text box there. What you can do though is have your field with the IIF in it be a calculated field. Then sum on the calculated field.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-15 : 16:03:32
|
hmm...as in this:=Sum(IIf(Fields!PriorityID.Value="Low",IIf(Len(Fields!AICD.Value) < 1,DateDiff("d",Fields!AIOD.Value,Now()),DateDiff("d",Fields!AIOD.Value,Fields!AICD.Value)),0))???That gives me a #Error in that footer. Man access had this :(...why couldnt Microsoft just integrated access into this :(Been so much more flexible with event handling :(. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-15 : 16:06:45
|
| MS actually purchased this product from another company. And no, put your statement in a calculated field. Then you can refer to the field like you do with the others. So add a field, specify it as calculated, then:IIf(Fields!PriorityID.Value="Low",IIf(Len(Fields!AICD.Value) < 1,DateDiff("d",Fields!AIOD.Value,Now()),DateDiff("d",Fields!AIOD.Value,Fields!AICD.Value)),0)Now your Sum becomes:Sum(Fields!CalculatedFieldName.Value)Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-15 : 16:17:16
|
| Ya I ended up trying that but I keep getting #Error next to it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-15 : 16:18:19
|
| I'll try it out on my laptop.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-15 : 16:19:18
|
| I just got it T, thank you, had the darn field in the wrong group by.THanks for the consistant help.jon |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-15 : 16:22:53
|
| Jon, it worked fine for me. I created a calculated field that contained my IIF logic on a field. My detail textbox pointed to the calculated field. Then in my field in the header, I had the sum of the calculated field. I get the correct results and no error.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-15 : 16:24:01
|
| Oops, just noticed your reply. Glad to help.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-15 : 16:24:06
|
| Ya like I said I had it placed in the wrong group so it was getting a bit confused...luckily I'm not doing any accounting reports ;) you might get a pay check for a buck if I wrote the report :).Works a treat, and I guess it is a bit similiar to access in this regard.Jon |
|
|
|
|
Is Null or something...