Converting empty string value to date

[devnaren]

Hi All,
I need to convert empty or null values to '00/00/0000' in report, the column datatype in database is varchar2.

Table:

EmployeeID EmployeeHiringDate (mm/dd/yyyy)
1 01/12/2007
2 02/15/2007
3 03/08/2007
4 11/10/2007
5 09/22/2007
6 null
7 null
8 null
9 null
10 null


On the report i created two parameters {?StartDate} and {?EndDate} which are in Date format.

I created a formula for {@EmployeeHiringDate} as

If isnull({EmployeeHiringDate}) then
'00/00/0000'
else
{EmployeeHiringDate}

In Record Selection formula, the formula is written as
{@EmployeeHiringDate} in CStr({?StartDate}) to
CStr({?EndDate}).

when i am executing this report from 01/01/2007 to 03/09/2008, it returns only one record i.e.,

4 11/10/2007


How to get records which are in the range of startdate to enddate.

 

[Madawc]

Try

{EmployeeHiringDate} in [{?StartDate}) to {?EndDate}

I am assuming that the parameters are dates, make them so if they are not. And that you don't want the nulls.

Also check there is no confusion between mm/dd/yy and dd/mm/yy, US and British defaults


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10