Null Date Not Working 1

[govmule]

Hello,

I am using Crystal Reports 8.5 connected to an Informix Dynamic Server 7.3 via native drivers.

My report uses three tables WIA_APP, WIA_CASE & WIA_EXIT.

I want to show clients who are considered Carry In's. These are clients who enrolled prior to a parameter called 'Start Date' and who exited on or after 'Start Date' or who have not exited at all. There is also a selection based upon Grant (funding stream) which is not effecting this problem.

Here is the selection formula:
-----------------------------------------------------------
{wia_case.wia_grnt_cd = {?Grant Code} and
{wia_case.enrlmt_dt} < {?Start Date} and
({wia_exit.exit_dt} >= {?Start Date} or isnull({wia_exit.exit_dt}))
-----------------------------------------------------------
Here is the show sql:

-----------------------------------------------------------
SELECT
wia_app.wia_app_num, wia_app.wia_agcy_cd, wia_case.wia_case_num, wia_case.wia_grnt_cd, wia_case.enrlmt_dt, wia_agcy.wia_agcy_cd, wia_agcy.agcy_nam, wia_exit.exit_dt
FROM
sora@forest:informix.wia_app wia_app,
sora@forest:informix.wia_case wia_case,
sora@forest:root.wia_agcy wia_agcy,
OUTER sora@forest:informix.wia_exit wia_exit
WHERE
wia_app.wia_app_num = wia_case.wia_app_num AND
wia_app.wia_agcy_cd = wia_agcy.wia_agcy_cd AND
wia_app.wia_app_num = wia_exit.wia_app_num AND
wia_case.wia_grnt_cd = '609' AND
wia_case.enrlmt_dt < "7 1, 2004" AND
(wia_exit.exit_dt >= "7 1, 2004" OR
wia_exit.exit_dt IS NULL)
ORDER BY
wia_app.wia_agcy_cd ASC
-----------------------------------------------------------

This produces only records that have an exit date. Since I have an outer join to the exit table I was hoping to get clients with no exit date also.

When I replace "isnull({wia_exit.exit_dt}) with {wia_exit.exit_dt} = date(0,0,0) the date shows up in the show sql as 1,1,1900

Any ideas?

As always, this forum and it's members are much appreciated.

Thanks,

Jack

 

[synapsevampire]

At first glance it appears that you're suffering from the CR nuisance whereing null checking needs to be performed first, try:

(
isnull({wia_exit.exit_dt}))
or
({wia_exit.exit_dt} >= {?Start Date}
)
and
(
{wia_case.wia_grnt_cd = {?Grant Code}
)
and
(
{wia_case.enrlmt_dt} < {?Start Date}
)

-k

 

[govmule]

Hey SynapseVampire,

As usual you are correct & awesome. I should have posted hours ago instead of torturing myself.

I was wondering why it was working in some of my other formula's.

A million thanks!

 

[synapsevampire]

Oops there's an extra paren:

(
isnull({wia_exit.exit_dt}))
or
{wia_exit.exit_dt} >= {?Start Date}
)
and
(
{wia_case.wia_grnt_cd = {?Grant Code}
)
and
(
{wia_case.enrlmt_dt} < {?Start Date}
)