Difficult If-Then-Else statement with Dates

[southbean]

Hello All,


I’m having difficulty with an If-Then-Else statement. I can get the correct value for some records but not all.

My report is fairly simple. The report returns records for every day of a user defined date parameter. The formula I’m trying to write needs to determine weather or not a person was ‘certified’ on a particular date.

Name:		Certification:		Certification Date:		Expiration Date:
Bill Jones   CPR			       01/01/2008			     02/28/2008
Sam Smith	First Aid		     01/01/2008

In the above example, if the date parameter for the report was 10/01/2007 – 03/01/2008 then Bill Jones would not be certified on 10/02/2007, would be on 01/02/2008 and would not be on 03/01/2008. That’s relatively easy.

The part I’m having difficulty with is if the ‘Expiration Date’ is NULL then that needs to be interpreted as ‘Certified’ (i.e., an open-ended certification). Therefore, if the date parameter for the report was 10/01/2007 – 03/01/2008 then Sam Smith would not be certified on 10/02/2007, would be on 01/02/2008 and would also be on 03/01/2008.


Any/all help or suggestions would be greatly appreciated!

- LM

 

[IanWaterman]

AS you have not posted your existing formula its difficult to interpret how to build.

Some where in your formula you will need to add the bit below. This must appear before any clause which tests 'Expiration Date' as Crystal formula fail as soon as they hit a Null unless they have been dealt with as such

if isnull(ExpirationDate) then 'Certified' else ...

Ian

 

[southbean]

Ian,

Thanks very much! That piece of information really helped.

Thanks again,

- LM