null date and null value to default

[atarrqis]

CR8.5 and Oracle
For other reasons, I have to check "null value to default" in this report. I have a real_date field which is sometimes null. I bring it into the report and it is sometimes null. I try and build a formula - blank_date -If IsNull({real_date}) then today else {real_date}. This formula is sometimes null (matches real_date). How can I make this default to today?
Thanks.

 

[synapsevampire]

I would guess that there's a default for the date, so try:

If {real_date} = cdate(0,0,0)
or
totext({real_date}) = "" then
today
else
{real_date}

But check to see what realdate contains, it shouldn't ISNULL because of your null values to default. I think that the trick is to dicover what's in the date when it's blank, so check the various possibilities.

WHat does Crystal display when there's nothing in it? That's what you need to check for.

-k

 

[Turkbear]

Hi,
With that setting then the test would be

If {real_date} = Date(0,0,0) 
then
 today else
 {real_date}.

From the help file:

Default values
Number
0

Currency
$0

String
"" //The empty string

Date
Date (0, 0, 0) //The null Date value

Time
The null Time value. Value held by an uninitialized Time variable.

DateTime
The null DateTime value. Value held by an uninitialized DateTime variable.

Note: It is not recommended that your formulas rely on the values of uninitialized range or array variables.



Hope it helps...




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

 

[atarrqis]

Thanks to both replies. Neither the cdate or date solution
worked but the totext..."" did. I do not understand why but I'm happy.

 

[Turkbear]

If "" did work, perhaps the field is not really a Date type in the database but a string that looks like a date



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

 

[atarrqis]

Using SQLPlus, it's a Date.

 

[synapsevampire]

<shrug>

Might be the connectivity, one should use either the Crystal supplied ODBC or Crystal's native connectivity (preferred).

The Oracle driver does sTRanGe things, and returns incorrect results.

btw, cdate and date are the same thing.

-k

 

[atarrqis]

ok, thanks.
We have to use Oracle driver for this.
Maybe I'll eventually learn to watch out for these sorts of things.

 

[Turkbear]

Hi,
Please explain what business rule they have forced on you to require an inefficient and often error-prone connection method.
If the ODBC driver works, so will the native one..Crystal does not care and neither will the data.






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

 

[synapsevampire]

Again, you can't use the Oracle driver safely, so whoever is making this decision is dooming you to failure.

Crystal states explicitly that you should not use it, and I have tons of test cases wherein it will return the wrong data, loss of precision of values, and much moire scarey, different results for the same query.

You do NOT have to use the Oracle based driver, and more importantly, if you do, you'll get the wrong results eventually, if you're not already.

Fire the architect and proceed.

-k