Null Date in Formula

[jbarbato]

Hi there! I am using Crystal Reports XI, with Oracle 10g database...

I am having a problem with a parameter: {?Date_From}

Our users input parameters in a Flex application. The parameters are then passed through to Crystal Reports. If the user does not select anything for {?Date_From}, 01/01/1800 is passed to Crystal so that all records are returned.

The data looks like this:
Package Date 1 Date 2 Date 3 Date 4
11111 02/10/2005 02/15/2005 02/20/2005 02/25/2005
22222 01/14/2005 01/17/2005 01/22/2005 01/28/2005
33333 12/05/2004 12/15/2004 01/01/2005
44444 11/12/2004 11/20/2004 11/27/2004 12/02/2004

If any one of the 4 dates associated with a package is >= {?Date_From}, the entire row should show up. As you can see, it is possible for a date to be null, as in package 33333.

My command statement is simple:

select package, date1, date2, date3, date4 from packages

I have a formula field, {@Date_From} that is equal to 01/01/1800.

My select expert formula is as follows:

(If {?Date_From} = {@Date_From} Then True
Else (
({Command.date1} >= {?Date_From}) or
({Command.date2} >= {?Date_From}) or
({Command.date3} >= {?Date_From}) or
({Command.date4} >= {?Date_From})
)
)

It works except when there is a null value in one of the date fields.

For example, if the user selects {?Date_From} = 12/15/2004, Packages 11111, 22222, and 33333 should show up... This works fine.

On the other hand, if the user selects {?Date_From} = 01/01/2005, Packages 11111, 22222, and 33333 should show up again. But all I get are packages 11111 and 22222.

Any suggestions? Thanks

 

[synapsevampire]

You have a few options here.

I'd suggest that you convert the date fields that are null to some static value to simplify coing this out.

select package, iif(date1 is null,<some default date value>,date1) date1, iif(date2 is null,<some default date value>,date2) date2,iif(date3 is null,<some default date value>,date3) date3, iif(date4 is null,<some default date value>,date4) date4

-k

 

[Madawc]

I'd do it as a set of boolian formula fields. @GoodDate1 as

not isnull({Command.date1})
and
{Command.date1} >= {?Date_From}

In the selection, say

@GoodDate1 
or @GoodDate2 
or @GoodDate3
or @GoodDate4

Madawc Williams (East Anglia, UK)