Calc Statement to give a Text Return

[agaperrk]

I am trying to Create a Flag, as to notify me when a expiration date is flagged with some text.

The Calc Statement I am using is

If ( Get ( CurrentDate ) > Nurse Xdate + 340;"Expiring";If ( Get ( CurrentDate ) > Nurse Xdate + 365;"Expired";"") )

I am doing something wrong.

Thanks in advance.

[LaRetta]

If() only allows two choices, not three and nested If() is overkill. Use Case() instead and, for those records which don't match either Expiring or Expired, you don't need to enter a blank result of "". So your calc would be:

Case (

Get ( CurrentDate ) > Nurse Xdate + 365 ; "Expired" ;

Get ( CurrentDate ) > Nurse Xdate + 340 ; "Expiring"

)

Notice I reversed the criteria ... Case() will stop evaluating when it hits the first true so it would have produced Expiring on ALL Nurse Xdates > Get ( CurrentDate ) + 340.

Be sure to make this calculation unstored (in Storage Option).

LaRetta

Edited July 24, 2008 by Guest
Corrected

[agaperrk]

If() only allows two choices, not three and nested If() is overkill. Use Case() instead and, for those records which don't match either Expiring or Expired, you don't need to enter a blank result of ""

So your calc would be:

Case (

Get ( CurrentDate ) > Nurse Xdate + 365 ; "Expired" ;

Get ( CurrentDate ) > Nurse Xdate + 340 ; "Expiring"

)

Notice I reversed the criteria ... Case() will stop evaluating when it hits the first true so it would have produced Expiring on ALL Nurse Xdates > Get ( CurrentDate ) + 340.

I hope you realize that there may be records on EACH side of this calculation period who will produce no result at all? I'm sure that is probably fine but I wanted to point it out. Also, be sure to make this calculation unstored (in Storage Option).

LaRetta

I guess what you are saying which I just found out is that if you type in Yesterday's date it still will show Expired. Not sure what to do about that I assumed it would count the days from the expiration date. I need to put some thing in to just show a blank if nothing is needed.

[LaRetta]

Well I started to address both sides of the calculation currently unidentified. But I removed it because it isn't needed, unless the logic of the calculation isn't right. So give me the rules again ... here is what currently is produced:

If today is 7/23/2008 and Nurse XDate is 7/26/2007 then one year has not passed but it is getting close. So the result is "Expiring." If today is 7/23/2008 and Nurse XDate is 7/21/2007 then the year has passed and it is "Expired."

A blank NurseXDate produces NO result and if the NurseXDate isn't due yet (it's still within the yearly period) then the date is blank.

How do we need to adjust it?

[agaperrk]

Well I started to address both sides of the calculation currently unidentified. But I removed it because it isn't needed, unless the logic of the calculation isn't right. So give me the rules again ... here is what currently is produced:

If today is 7/23/2008 and Nurse XDate is 7/26/2007 then one year has not passed but it is getting close. So the result is "Expiring." If today is 7/23/2008 and Nurse XDate is 7/21/2007 then the year has passed and it is "Expired."

A blank NurseXDate produces NO result and if the NurseXDate isn't due yet (it's still within the yearly period) then the date is blank.

How do we need to adjust it?

Yeh you got it right! But when I put in the next xdate to be 7/2/09 It will still show expired. So there is a step missing or something a little off. Maybe it is using the current date for the next expiration date.

[LaRetta]

Make the calculation unstored and then check it again. Because when I put in 7/2/2009 as the Nurse XDate and today is 7/23/2008 then the calculation produces nothing.

UPDATE: Also remember that I REVERSED the criteria in the calculation so check that again.

Edited July 24, 2008 by Guest
Added update

[agaperrk]

Make the calculation unstored and then check it again. Because when I put in 7/2/2009 as the Nurse XDate and today is 7/23/2008 then the calculation produces nothing.

UPDATE: Also remember that I REVERSED the criteria in the calculation so check that again.

I did and it is still showing expired. It will show expired on every thing. I unchecked the unstored. I just copied and pasted the calc in the field.

[LaRetta]

I suspect that your Nurse XDate is set to text and not date.

[agaperrk]

Case (

Get ( CurrentDate ) >Nurse Xdate + 365 ; "Expired" ;

Get ( CurrentDate ) > Nurse Xdate + 340 ; "Expiring"

)

This is what I put in.

[agaperrk]

That was it I had it set to text.

Thanks,

Would like to be able to use your expertise more often.

[LaRetta]

Visit FM Forums as often as you like, Randy. There is no charge and you will find an incredible bunch of talented Developers here, giving of their precious time to assist.

LaRetta :wink2: