How to accept null date value when using Crystal SQL Query?

[pastasalad]

I have a Crystal 8 report, based on a SQL Query on a DB2 UDB database, which includes 4 parameters:
- Parameter1
- Parameter2
- Start Date
- End Date

All of the parameters are optional, thus I have used:
({table}.{field} = '{?Parameter1}') OR ('{?Parameter1}' = '')

to disregard any parameter fields that the user has left blank.

This works OK for Parameter1 and Parameter2 (both string fields), but it fails when I use it with the two dates.

How do I write the equivalent of ('{?Parameter1}' = '') for a date field? I don't want to put the date selection in the report definition because the database is very large and I need everything server-side.

Thanks for any suggestions.

 

[synapsevampire]

Assuming that you're speaking of the record selection formula, set a default value for the dates, as in 1/1/1970 and try taking the following approach:

//Strings:
(
If ({table}.{field} <> &quot;&quot; then
{table}.{field} = {?Parameter1}
else if
If ({table}.{field} = &quot;&quot; then
true
)

and

// Dates
(
If ({table}.{field} <> cdate(1970,1,1) then
{table}.{field} = {?Parameter1}
else if
{table}.{field} = cdate(1970,1,1) then
true
)

-k

 

[pastasalad]

Thanks Synapse

In fact everything I referred to was in the Crystal SQL statement, rather than the report selection formula.

I have now got the selection to ignore blank parameter date fields, however, by using:

({table}.{field} >= '{?Start Date}') OR ({?Start Date} IS NULL)