Need formula to select records by date range

[rorymo]

Hi,

I need a formula to select records that dated between
Jan. 1, 2004 and Feb. 29, 2004.
The field I am using is "Open Date" which is defined as a
numeric field.
We use this formula to convert the date:
DateAdd ("s",({call_req.open_date}-21600),#1/01/1970#)

I created this formula:
({@Open Date} >= (DateSerial (2004, 1, 1)))
and
({@Open Date} <= (DateSerial (2004, 2, 29)))

I then used the formula in the record selection, but no
records were returned.

There are definitely records since this is a help desk
database.

Any help would be appreciated. I'm using CR9 with SQL
Server db.

Thanks,
rorymo

 

[tdong]

use selection expert it is of this format
{vwCPIssuesLiquidityReport.maturity_date} in DateTime (2003, 12, 15, 00, 00, 00) to DateTime (2004, 01, 12, 00, 00, 00)

has to be datetime

 

[dgillz]

{@OpenDate} in date(2004,1,1) to date(2004,2,29)

There is no requirement to be datetime as suggested above.

If this formula does not work, then place the field {@opendate} on your report and see what you are getting as a result. I am confused as to how {OpenDate} could be a numeric field as you suggested, it should be a date field.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[rorymo]

Thanks for everyone's replies.

I will try {@OpenDate} in date(2004,1,1) to date(2004,2,29).

BTW, I don't know why the date is defined as numeric,
it was defined as such when we got the database, and
it (db) was not developed in-house.

Thanks,
rorymo

 

[rorymo]

I just tried this formula and it still didn't work.
I am going to put the dates in a "Start date - End date"
parameter for now.

Thanks,
rorymo

 

[tdong]

Passing value using Prompting USER FOR INPUT here is how it done


Select Insert->Field Object->Right click parameters and select new->name it

Select the data type that is consistent with the type of data being filtered on the database.

Example: Date

If you want a range of dates, select the Range check box, and Crystal will propmt for 2 dates.

Now select Report->Edit selection formula->Record

Enter in something like:

{table.date} = {?My DateParm}

Run the report.