Date parameter will not work for a range

[Former Member]

Hello All,

I’m a newbie to Crystal. After searching the web I have not
been able to find an answer to my problem.

My date parameter will not work for a range (i.e. one month), but it does work when the range entered is from the beginning of the database to present.

I have created 5 different reports with a command that has {?FromDate} and {?ToDate} parameters. These reports are identical with the exception of the Description field that I’m filtering on. When I created the 6^th report and changed the description to ‘RELOCATE VALVE’, the report will only work if I enter the start date as the beginning date of the database, and end date as the end of this month. So basically the date range is for the whole database.   If I enter a different range for a one month period, the report returns no records. Not even an error message, just a blank page except for report heading which are just text.  I have deleted the default values in the parameter and receive the same results. I have checked the data, even re-entered the dates in the database, and I get the same results. I only have 6 records for this particular description, but I wouldn't think that should make a difference. I don’t understand why it works correctly in my other 5 reports and not this one.

My version of Crystal is 11.0.  Here is my query with the parameters:

   WHERE  domainid = 2

              AND w.description = 'RELOCATE  VALVE'

              AND w.actualfinishdate BETWEEN {?FromDate} AND {?ToDate}

   ORDER BY w.workorderid DESC;

Any assistance is greatly appreciated.

Vicki

[Former Member]

I would start by makeing sure that the fields you are working with are all  the same data type ( ie they are all actually  Date/time fields in the same format. )    Sometimes "dates"  are actally string fields that only look like dates.

Also ........   as a "Heads up".    Working with the begining of the month is easy   but the "end of the month" can be problematic since it occurs on different dates.   The best way to deal with it is that the "end of month"  is "The first of the next month less one second"             

[Former Member]

Ted,

I did check the data type and it is date/time, so that isn't the problem .  As I indicated this date field is the exact same field in all my other reports that works just fine. That is one reason this is so baffling.

Thanks for the "Heads up" on the end of the month date.

Thanks for your reply.

Vicki