default value for null date

[atarrqis]

I'm using CR 8.5 against an Oracle db. I have a parameter as a date that looks at a date field which may be null (literally). I've read other threads about using the "Convert Null field value to default" and suggestions of setting the default to some far out date. This is what I want to do but cannot figure out how to 'set the default'. My understanding is, if this is checked and it's a date field that is null, CR will treat this as Date(0,0,0). Where do I set this as Date(2049,1,1)? If I need a formula, is it specific to the date field I'm testing - so I'd need a formula for each date field or is there one place I can set this?

I tried putting and IF..then in the Record Selection for the specific field I'm interested in and then using the parameter but this didn't work so I'm assuming that was a bad guess.

Thanks.

 

[CR85user]

if then should work fine..

if isnull({yourdate.field}) then date(2049,1,1) else {yourdate.field}

Uncheck the "Convert Null field value to default" 1st as well.

It is always good practice to check for nulls 1st if they may be present in the db.


J

 

[atarrqis]

I must have missed something because when I use this formula I get "The result of selection formula must be a boolean." What am I doing wrong?

 

[smcnulty2000]

It sounds like you are plugging the formula into the wrong place. That formula returns a date, which is what you use in a formula field.

If you put this in by going to 'report/selection formulas/record' then that is the problem. That place is for stopping specific records from arriving (to put it simply). You would use that if you wanted the records with the nulls to not even be read.

The purpose of the listed formula is to create a formula field (look in your field explorer, just below 'database fields' is 'formula fields', right click on this and select new).

Try putting the formula in there and then dragging the resulting formula to the details section of your report. What it should do is: for every record it will either return the real date or the fake date you supplied. But it will get every record, and then process it.

ScottM.

 

[atarrqis]

But that is what I want to do - stop the records from even being brought in. I have a parameter that is a date, the user enters this date and the report needs to bring back every record greater than this date including all records with a null date.

 

[smcnulty2000]

Okay,

Believe it or not you are going to be bringing in all the records and then turning the nulls into your relevant date after they are inside of crystal.

Put a formula like this in your record selection:

{yourdate.field}>={?yourparameter}
or
isnull({yourdate.field})


...Which pulls all the records in that aren't fitting that criteria. (As it looks at each record it compares it to your parameter and the isnull question. If it is either null or greater than your parameter, the answer is yes and it gets brought in, otherwise it gets ignored.)

Then, if you still want the nulls converted, put the other formula into a formula field as described. If you were going to group on the date, group on the formula field that you make instead.

You can't convert data at the selection criteria. You can't make crystal convert data back on the database. You have to get them in before you can do any conversion to them. And the selection criteria is at the threshold but it isn't inside of crystal yet.

Clearer?

Scott M.

 

[synapsevampire]

Believe it or not, that formula may not work properly in CR 8.5.

Change it to:

(
isnull({yourdate.field})
or
{yourdate.field}>={?yourparameter}
)

This is due to how CR 8.5 handles nulls and interprets the Record Selection formula.

But I don't think that this isn't a good solution here anyway.

Set the default value for the date parameter to a known value outside the bounds of reasonable data input, such as 1/1/1970

In the record selection formula use:

(
if {?MyDatePArm} <> cdate(1970,1,1) then
(
not(isnull({table.date}))
and
{table.date} = {?MyDatePArm}
)
else
if {?MyDatePArm} = cdate(1970,1,1) then
not(isnull({table.date}))
)

To set default values for a parameter, select set default vales while in the parameter creation/editing window.

If this is wrong, consider less descriptions and posting more technical information.

Crystal version
Database/connectivity used
Example data
Expected oputput

I reread your post twice trying to determine what it is that you wanted and the language barrier was too great to clearly overcome.

This: " I've read other threads about using the "Convert Null field value to default" and suggestions of setting the default to some far out date. This is what I want to do but cannot figure out how to 'set the default'." sounds liek you simply want to know how to set a default value, which won't filter anything or do anything to speak of other than change the default prompt.

-k