If-Then-Else and Null Dates

[szeiss]

I'm using CR8 and Oracle backend. I'm having problems with my if statements. Here's my formula, which doesn't work:

If not isnull(CS_End) and isnull(BS_End) then ""
Elseif
If isnull(CS_End) and not isnull(BS_End) then ""
Elseif
If not isnull(CS_End) and not isnull(BS_End) then
datediff("d", BS_End, CS_End) = 0 then
"Yes"
Else
"No"

Basically, if one or both date fields are empty I want an empty string. If both date fields have a date in them then I want to do the datediff function.

The BS_End is a shared variable from a subreport and I've tried just testing that one value using the isnull function and printing an "N/A" instead of an empty string and I get nothing. But, the shared variable does work with the datediff function when both date fields are not empty. Can anyone help.

Thanks,
Sherry

 

[darwin101]

I may be wrong, but why are you using "if" after the "elseif" line? I have not seen that used in such a manner before.

 

[LucyColey]

Try This...

If Not IsNull(CS_End)
And Not IsNull((BS_End))
Then CStr(DateDiff("d", CS_End, BS_End))
Else ''

If that doesn't work, then your problem has something to do with the shared variable.

 

[szeiss]

I think you may have misunderstood my question, or I didn't explain myself. Here is an example:

CS_End BS_End
#1 3/28/2001 ""
#2 "" 11/12/2001
#3 "" ""
#4 11/05/2001 7/17/2000

In #1-#3 my formula should evaluate to " " because either or both of the fields have an empty date field. In #4 it should do the calculation.

Thanks,
Sherry

 

[KPharm]

I think what darwin is saying is that your syntax is a little off. Try:

(
If not isnull(CS_End) and isnull(BS_End) then
""
ElseIf isnull(CS_End) and not isnull(BS_End) then
""
ElseIf not isnull(CS_End) and not isnull(BS_End) then
(
if datediff("d", BS_End, CS_End) = 0 then
"Yes"
Else
"No"
)
)

 

[szeiss]

I copied it exactly as is and it keeps telling me I'm missing &quot" after the first set of "".

 

[KPharm]

Try it without them. I may have been looking at a non-formula-field example in my report instead of what you need:

If not isnull(CS_End) and isnull(BS_End) then
""
ElseIf isnull(CS_End) and not isnull(BS_End) then
""
ElseIf not isnull(CS_End) and not isnull(BS_End) then
if datediff("d", BS_End, CS_End) = 0 then
"Yes"
Else
"No"

I can't vouch for the datediff expression, though.

 

[MalcolmW]

Are BS_End and CS_End date variables or string variables?
Are you declaring the variables in your formula?
You could also simplify (and speed up) your formula with:

Shared DateVar BS_End ;
Shared DateVar CS_End ;
If Not IsNull(CS_End) AND Not IsNull((BS_End)) then
(If CS_End = BS_End then
"Yes"
Else
"No&quot
Else
""
Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[szeiss]

They are datetime values.

 

[MalcolmW]

OK - what output does this produce?
Shared DateTimeVar BS_End ;
Shared DateTimeVar CS_End ;
If Not IsNull(CS_End) AND Not IsNull((BS_End)) then
(If DateDiff("d", CS_End, BS_End) = 0 then
"Yes"
Else
"No&quot
Else
"Null value in one or both variables"
Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[szeiss]

This gives me an error "The remaining text does not appear to be a part of the formula" and the cursor stops on the first line before BS_End, which is a shared DateTime variable from a subreport. I don't know if that matters or not.

 

[MalcolmW]

Hmm, my brain is taking a holiday today. Crystal doesn't like dealing with Nulls as variable results. So, use the null datetime value as below:

Shared DateTimeVar BS_End ;
Shared DateTimeVar CS_End ;
If (CS_End <> DateTime(0,0,0,0,0,0) AND BS_End <> DateTime(0,0,0,0,0,0)) then
(If DateDiff(&quot;d&quot;, CS_End, BS_End) = 0 then
&quot;Yes&quot;
Else
&quot;No&quot
Else
&quot;Null value in one or both variables&quot; Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[szeiss]

Same error.

 

[MalcolmW]

Hmm, I pasted that into CR 8.5 and it ran. However, it produced a quirky result, so it needs a tweak. I had effectively populated the variables with null datetime values, but Crystal did not view this as equivalent to DateTime(0,0,0,0,0,0). Nor could I use the test for true Null values IsNull(variable) because that syntax is not allowed in formulas.
To get around this, I created two formulas, @BS_End and @CS_End. In each was just the variable declaration
ie
Shared DateTimeVar CS_End
I then went back to
//start
If IsNull({@CS_End}) AND IsNull({@BS_End}) then
(If DateDiff(&quot;d&quot;, {@CS_End}, {@BS_End}) = 0 then
&quot;Yes&quot;
Else
&quot;No&quot
Else
&quot;Null value in one or both variables&quot;
//end
which produced the correct result (the text blurb about null values).
I'm still not sure why you are getting the error you mentioned - typically that occurs when you don't declare a variable in a formula in which it is used.

Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[szeiss]

I have gotten pulled off on some other things. Will try your suggestion just as soon as I can. Thanks.