partsgb.martinw Posted November 20, 2000 Share Posted November 20, 2000 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 Link to comment Share on other sites More sharing options...
Kurt Knippel Posted November 21, 2000 Share Posted November 21, 2000 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 =-=-=-=-=-=-=-=-=-=-=-=-= Link to comment Share on other sites More sharing options...
partsgb.martinw Posted November 21, 2000 Author Share Posted November 21, 2000 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 Link to comment Share on other sites More sharing options...
Kurt Knippel Posted November 21, 2000 Share Posted November 21, 2000 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 =-=-=-=-=-=-=-=-=-=-=-=-= Link to comment Share on other sites More sharing options...
Kurt Knippel Posted November 21, 2000 Share Posted November 21, 2000 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 =-=-=-=-=-=-=-=-=-=-=-=-= Link to comment Share on other sites More sharing options...
JerrySalem Posted November 22, 2000 Share Posted November 22, 2000 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 Link to comment Share on other sites More sharing options...
partsgb.martinw Posted November 22, 2000 Author Share Posted November 22, 2000 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).] Link to comment Share on other sites More sharing options...
Kurt Knippel Posted November 22, 2000 Share Posted November 22, 2000 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 =-=-=-=-=-=-=-=-=-=-=-=-= Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 8563 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now