Problem with Date parameters 1

[lana123]

Hi,people!
I'm working with Crystal 9 and I have problem with sending null values in Date parameter.
This is my formula:
if {?parameter}='ACTIVE' then
({table.field} = Date (0, 0, 0))
else if {?parameter}='INACTIVE' then
({table.field} <> Date (0, 0, 0))
else
true

I'm getting such results in a query:
WHERE (&quot;table.field&quot;>=TO_DATE ('30-12-1899 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND &quot;table.field&quot;<TO_DATE ('30-12-1899 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))
How I can show null values for date?
I'll appreciate any answer.Thanks in advance.
Lana.

 

[synapsevampire]

This formulas design currently does nothing on any level.

Perhaps you could share the intent?

Try:

(
if {?parameter} = 'ACTIVE' then
{table.field} > Date(0, 0, 0)
else if {?parameter} <> 'ACTIVE' then
true
)

-k

 

[synapsevampire]

This formulas design currently does nothing meaningful on any level.

Perhaps you could share the intent?

Try:

(
if {?parameter} = 'ACTIVE' then
{table.field} > Date(0, 0, 0)
else if {?parameter} <> 'ACTIVE' then
true
)

-k

 

[lana123]

Hi and thank you for answering me.
The business rule for this report are:
if {table.field}is NULL then we selecting only ACTIVE data.If table.field}is
NOT NULL then we selecting only INACTIVE data.Otherwise we select both:ACTIVE and INACTIVE.
I have problem with Date(0,0,0)This gives me very strange results.Thanks.
Lana.

 

[synapsevampire]

You don't check the tabe first and then compare it to a parameter, you select the parameter and compare it to the database.

Do you mean that if the user has selected ACTIVE then you want null fields, and if they select Inactive then you want those that are NOT NULL, and if they choose neither, you want everything?

Try:

(
if {?parameter} = 'INACTIVE' then
{table.field} > Date(0, 0, 0)
else if {?parameter} <> 'INACTIVE' then
true
)
and
(
if {?parameter} = 'ACTIVE' then
isnull({table.field})
else if {?parameter} <> 'ACTIVE' then
true
)

This assumes that the column is null.

-k

 

[lana123]

It works!
Thank you VERY MUCH!
Lana.