Date formula....set to "" if it is 01/01/1990 1

[cathiec]

my database stores the date as 01/01/1990 if no date is entered on data entry.

when i am reporting on this information the date field shows 01/01/1990 if no date was entered. I want to create a formula that will display "n/a" or just and empty string if the date is equal to 01/01/1990 or display the date if it is not 01/01/1990.

i wrote a formula like this:

if {tblAccidentTrackIT_Data_Accident.strDateLastJSA} = 01/01/1990
then ""
else
{tblAccidentTrackIT_Data_Accident.strDateLastJSA}

but i get the error a date is expected here and i think that is because i am entering a string in the then part and a date in the else part of the expression. i would really appreciate help with this. using crystal 10

thanks and regards

 

[TheBlondOne]

Hi there,

try using the following code.

if {tblAccidentTrackIT_Data_Accident.strDateLastJSA} = 01/01/1990
then totext("")
else
{tblAccidentTrackIT_Data_Accident.strDateLastJSA}

HTH

 

[cathiec]

thanks for your reply but i am still getting the error "a date is required here"

 

[TheBlondOne]

ok that was an error on my part. What i've done in this one is basically instead of inserting a "" I've used a date of 01/01/0001. The reason I've done this is because the error your receiving is to do with the fact that there are date and string outputs in the same formula, so instead of inserting a "" I've used a date of 01/01/001.

if {Date} = date(01/01/1990)
then date(01/01/0001)
else
{Date}


Then what you can do once that is done is if you format the field and go to the common tab, then click on the X2 next to suppress and then type

currentfieldvalue = date(01/01/0001)

What this will do is suppress the field if the date = (01/01/0001) in other words this will in effect be the equivilent of using "".

HTH

 

[TheBlondOne]

I've just ran the code I sent you and I found a little problem with it so here is a revised version, slightly long but it works. Also you will still need to keep the suppress condition in format field>>Common tab>> Suppress X2.

if
year({Date}) = 1990 and
month({Date}) = 01 and
day({Date}) = 01 then
date(01/01/0001)
else
{Date}


HTH

 

[lbass]

You could just conditionally suppress the date field by right clicking on it->format field->common->suppress->x+2 and entering:

{tblAccidentTrackIT_Data_Accident.strDateLastJSA} = date(1990,01,01)

-LB

 

[cathiec]

thanks TheBlondOne and Ibass....conditionally surpressing the date field worked a treat!

 

[sat6845]

if date({Command.START_DT}) = date('1/1/1900') then date(0,0,0) else {Command.LATEST_COP_START_DT}

it will display nothing....if the date is '1/1/1900'