Multiple Dates Within Date Parameter Formula Not Working Completely

[dazum]

I have a formula that I would like to show information related to a date as long as the date falls within the begin and end date parameters. I have 3 dates in the formula and it works for the first 2 dates in the formula, but if the 3rd date falls within the date parameters it's data does not show up in the report.
Below is the formula:

if {Sheet1_.SAR Date} in {?begin} to {?end}
then
{Sheet1_.SAR Date} & " " & {Sheet1_.SAR Time} & " " & {Sheet1_.Facilitator}
else
if {Sheet1_.90 Date} in {?begin} to {?end}
then
{Sheet1_.90 Date} & " " & {Sheet1_.90 Time} & " " & {Sheet1_.Facilitator}
else
if {Sheet1_.CP Date} in {?begin} to {?end}
then
{Sheet1_.CP Date} & " " & {Sheet1_.CP Time} & " " & {Sheet1_.Facilitator}

Is there a different way to write the formula to get data for the 3rd date (if it's within the parameters) to show on the report?
I am using CR 11.

 

[lbass]

What is the purpose of this formula? In what report section are you trying to display it? If this is a detail level formula, change it to:

(
if {Sheet1_.SAR Date} in {?begin} to {?end} then
{Sheet1_.SAR Date} & " " & {Sheet1_.SAR Time} & " " & {Sheet1_.Facilitator}
) &
(
if {Sheet1_.90 Date} in {?begin} to {?end} then
{Sheet1_.90 Date} & " " & {Sheet1_.90 Time} & " " & {Sheet1_.Facilitator}
) &
(
if {Sheet1_.CP Date} in {?begin} to {?end} then
{Sheet1_.CP Date} & " " & {Sheet1_.CP Time} & " " & {Sheet1_.Facilitator}
)

If any of these dates can be null, you would need to add a null check to each of the three clauses.

-LB

 

[Madawc]

Crystal lets you call formulas from other formulas, and it's best to make use of this. Assume you want Date1 for preference but Date2 if Date1 is blank, and also Date3 regardless of Date1 or Date 2, you could do
[Code @Date1_2]if {Sheet1_.SAR Date} in {?begin} to {?end}
then
{Sheet1_.SAR Date} & " " & {Sheet1_.SAR Time} & " " & {Sheet1_.Facilitator}
else
if {Sheet1_.90 Date} in {?begin} to {?end}
then
{Sheet1_.90 Date} & " " & {Sheet1_.90 Time} & " " & {Sheet1_.Facilitator}
else " "[/code]

if {Sheet1_.CP Date} in {?begin} to {?end}
then
{Sheet1_.CP Date} & " " & {Sheet1_.CP Time} & " " & {Sheet1_.Facilitator}
else " "

If @Date1_2 = " " 
   then If @Date3 <> " " then @Date3
                         else "No dates"
else If @Date3 <> " " then @Date1_2 " & " and " & "@Date3
                      else @Date1_2

@ShowDate ought to contain the output you want, but it's best to show everything while developing and tidy up once it is right.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[dazum]

I was able to get the formula working by accounting for null values of the other dates that did not fall within the date paramenters. Below is part of the revised formula:

if isnull({Sheet1_.90 Date}) and isnull({Sheet1_.CP Date})
and {Sheet1_.SAR Date} in {?begin} to {?end}
then
{Sheet1_.SAR Date}........

Thanks for all the suggestions from everyone!