If Not Null Statement for Dates 1

[Jewel142]

Hi -

I'm trying to create a form using Crystal 2008 (I'm a very new user to the software) and I'm having trouble with one of my fields. I'm pulling Employee information from our Sql database and some employees have term dates and others do not.

For the employees without term dates, I want the field left blank, but if they are terminated, I want the field to show the actual termination date. I've created the following formula but my form still returns the value of 01/01/1753 in the field if the employee isn't terminated. Help!

if not isnull({EMPLOYEE.TERM_DATE}) then {EMPLOYEE.TERM_DATE} else ""

PS - I am also new to the forum so thanks in advance for your help!!

 

[Turkbear]

Hi,
sounds like the database has a default value for dates when there is ni data ( NULL dates)..Try :

If ToText({EMPLOYEE.TERM_DATE},'MM/dd/yyyy') = '01/01/1753'
then
""
Else
ToText({EMPLOYEE.TERM_DATE},'MM/dd/yyyy')

Note:you may not need the ToText function, so test without as well, it will be faster if not needed.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Jewel142]

Thank you so much - it worked. I've been sitting here for 4 hours trying this formula every which way. I finally decided to go on-line and found the forum.

Thanks again and have a great day!

Jewel142

 

[Turkbear]

Hi,
Glad to help..Those oddities of database design can be baffling. You could check with your DBA ( if there is one) to see if that is a built-in default or one that is created
when the table is built.





To Paraphrase:"The Help you get is proportional to the Help you give.."