Date Range Problem in Cystal Reports 8.5

[crguy]

Hi there,

My report contains a field "termination_date", and I would like to have parameters that specify a range of dates so that all "termination_date"s in that range are picked.

Here is the formula that I am using, but it does not seem to work.

(IF {?Start Date} <> DATE ( 1900,01,01 ) AND {?End Date} <> DATE ( 1900,01,01 ) THEN
{Query.TO_CHAR(PERS.ACTUAL_RETIRE_DATE,'DD-MON-YYYY')} >= {?Start Date} AND
{Query.TO_CHAR(PERS.ACTUAL_RETIRE_DATE,'DD-MON-YYYY')} <= {?End Date}
ELSE IF {?Start Date} = DATE ( 1900,01,01 ) AND {?End Date} <> DATE ( 1900,01,01 ) THEN
{Query.TO_CHAR(PERS.ACTUAL_RETIRE_DATE,'DD-MON-YYYY')} <= {?Start Date}
ELSE IF {?Start Date} <> DATE ( 1900,01,01 ) AND {?End Date} = DATE ( 1900,01,01 ) THEN
{Query.TO_CHAR(PERS.ACTUAL_RETIRE_DATE,'DD-MON-YYYY')} >= {?Start Date}
ELSE
(ISNULL ({Query.TO_CHAR(PERS.ACTUAL_RETIRE_DATE,'DD-MON-YYYY')}) OR
{Query.TO_CHAR(PERS.ACTUAL_RETIRE_DATE,'DD-MON-YYYY')} > DATE ( 1900,01,01 )))

 

[kenhamady]

What version of CR?
Is this data from a QRY file?
What does the formula do now?

The IsNull has to be before the field is otherwised used in the formula.

Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[idle]

You also appear to be comparing dates with a char field. What data type is the field
{Query.TO_CHAR(PERS.ACTUAL_RETIRE_DATE,'DD-MON-YYYY')} ?
Is the date 1900/1/1 your default value for your parameters?
Your first Else If should probably be ... <= {?End Date}, not ... <= {?Start Date}.

 

[crguy]

I am using CR 8.5.
Yes it is coming from a qry file.
When I check for errors it says &quot;string required&quot;.

Any ideas?

 

[kenhamady]

Are you intentionally converting this date to a string in the SQL statement? That appears to be what the ToChar is doing.

If so, you have to compare the field to string parameters, not date parameters. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[crguy]

I am not quite sure what you mean by &quot;you have to compare the field to string parameters, not date parameters.&quot; Can you elaborate?

Thanks
crguy

 

[kenhamady]

If your field is a string value, and you want to say:
{field} > {?Param}

Then your parameter has to match the data type of the field. If one is a string the other has to be a string. If one is a date, the other has to be a date. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[crguy]

Thanks. I will give it a try.