Formula to create group

[micro2005]

Hello everyone!
I am using SQL server 2000 with Crystal Reports 10. I have to create a formula and then create a group on this formula. Essentially, I need to get the start date from a table based on certain events. For example, the first condidtion would be
written_notification is null, fad is null and fromal_filed is not null then table.startdate
This will return all the cases in stage 1 (written notification)
Second condition would be
investigation_start is null, investigation_end is not null and fad is null then table.startdate
This will return all the cases in stage 2 (investigation). I am not able to create the formula since the written_notification is a value in a table field. Similarly, fad is also another value in the same table field. I am not sure how to work with this? Can someone please guide me in the direction? I will really appreciate it.

 

[micro2005]

A little modification I dont want to return the date, I want to return the word 'written notification' for first stage and 'investigation' for the second stage. If you need more information please let me know.
Thanks!

 

[BobSuruncle]

Let's clarify.
You have (at least) the following 5 fields in your tables:
{SomeTable.written_notification}
{SomeTable.fad}
{SomeTable.fromal_filed}
{SomeTable.investigation_start}
{SomeTable.investigation_end}
Is this correct?

If so, you could do something like this:

If IsNull({SomeTable.written_notification})
and IsNull({SomeTable.fad})
and NOT IsNull({SomeTable.fromal_filed})
then "Written Notification"

Else if 
IsNull({SomeTable.investigation_start})
and IsNull({SomeTable.fad})
and NOT IsNull({SomeTable.investigation_end})
Then "Investigation"
else
"oops.We forgot these records."

Bob Suruncle

 

[lbass]

I am not able to create the formula since the written_notification is a value in a table field. Similarly, fad is also another value in the same table field.

Please identify the field and also show what the results of the field look like. You say that written_notification is a value in the field, as is fad. What does the value look like? Does it simply say "Written_notification" or "fad"? Is this field always populated with one or the other or can it be null?

-LB

 

[micro2005]

I am consulting my DBA and we might go with the option of creating a view for this. Thanks for your suggestions.