Date to Status

[partsgb.martinw]

Hello All,

I am trying to do the following. I have two date fields. First date field is ( Faxed Date ) secord field is called ( Reminder Fax date ).

The other field is called Status.

What i am trying to do is the following. From the date the first fax was sent + 60 days = status ( "Lapsed" ). From Reminder fax date + 21 days = ( "Expired " ) Both lapsed and expired will appear in my status field which is text.

Here is my [ CALC ] but it does not work. What am i doing wrong.

Case( IsEmpty( Fax Date to dealers ), "",Case( IsEmpty(Fax Date of Reminder ),"",

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed", ""))

However i want to link expired and lapsed into another field called status and not the calculation field..

Hope this makes sense..

Regards,

Martin

[Kurt Knippel]

quote:

---

Originally posted by partsgb.martinw:

Hello All,

I am trying to do the following. I have two date fields. First date field is ( Faxed Date ) secord field is called ( Reminder Fax date ).

The other field is called Status.

What i am trying to do is the following. From the date the first fax was sent + 60 days = status ( "Lapsed" ). From Reminder fax date + 21 days = ( "Expired " ) Both lapsed and expired will appear in my status field which is text.

Here is my [ CALC ] but it does not work. What am i doing wrong.

Case( IsEmpty( Fax Date to dealers ), "",Case( IsEmpty(Fax Date of Reminder ),"",

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed", ""))

However i want to link expired and lapsed into another field called status and not the calculation field..

---

I would break the thing into 2 seperate statements, since your existing case can have multiple winning combinations.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

)

Case(

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed",

""

)

They can be split into multiple fields or used in the same fields and joined with an "&".

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[partsgb.martinw]

quote:

---

Originally posted by captkurt:

I would break the thing into 2 seperate statements, since your existing case can have multiple winning combinations.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

)

Case(

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed",

""

)

They can be split into multiple fields or used in the same fields and joined with an "&".

---

Hi Kurt,

Tried what you have wrote above but it doesn't work. see script below.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

)

Case(

Case(Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED" & Status,

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed" & Status,

""

))

It does not change my status field to Expired or Lapsed.

Do you have another idea what i can try..

Regards, Martin

[Kurt Knippel]

quote:

---

Originally posted by partsgb.martinw:

Hi Kurt,

Tried what you have wrote above but it doesn't work. see script below.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

)

Case(

Case(Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED" & Status,

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed" & Status,

""

))

It does not change my status field to Expired or Lapsed.

---

I was not making one statement. I was splitting your convoluted CASE into two parts. Those would go in seperate fields, or ifs or whatever.

My point was breaking them up is what you need to do.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[Kurt Knippel]

quote:

---

Originally posted by JerrySalem:

Have you tried just a single case statment? I don't think you need a nested case at all.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed",

""

)

---

This was the original problem. There are 2 completely different set of criteria in this and it will not work as you intend.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=

[JerrySalem]

Have you tried just a single case statment? I don't think you need a nested case at all.

Case(

IsEmpty( Fax Date to dealers ), "",

IsEmpty( Fax Date of Reminder ),""

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed",

""

)

Try this.

Jerry

[partsgb.martinw]

quote:

---

Originally posted by captkurt:

This was the original problem. There are 2 completely different set of criteria in this and it will not work as you intend.

---

Hello Kurt,

OK, it seems that i cannot do this as a define field. What about putting this into scriptmaker and get this to run on a trigger via a button. I have a refresh monitor button that is used. Any ideas on the sort of script that i need to use. I feel that "If" statement will be needed and "Setfield" What do you think :?:??

Many Thanks,

Martin

[This message has been edited by partsgb.martinw (edited November 22, 2000).]

[Kurt Knippel]

quote:

---

Originally posted by partsgb.martinw:

Hello Kurt,

OK, it seems that i cannot do this as a define field.

---

OK, maybe I wasn't clear. This can be done. It is 2 difference criteria and need to be done in 2 statements. Either within 1 or 2 fields or script statements.

Here is how I would do this:

Case(

Status(CurrentDate) > Fax Date to dealers + 60, "Lapsed",

Status(CurrentDate) > Fax Date of Reminder + 21, "EXPIRED",""

)

This way the field is either evaluated with one of the two text answers or it is blank. You also have to be careful how you structure the case, as it will stop evaluating after the first matching item.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=