IsNull Function Not Working For Me...

[ChiTownDiva]

Here's my selection criteria:

@mod date} >= PrintDate and
left({COMPANY},2) like left({?Company},2) and
{PERSON_STATUS} = "A" and
{LOCAL_PERS_ID} like {?RC Code/Tracking Unit} and
(if {?Compliance Report Requested?} like "Antitrust" then
{MGT_LEVEL} like "M" else
{MGT_LEVEL} like {?Management Level}) and
{ADDRESS_STATE} like {?State} and
(if {?Compliance Report Requested?} like "Affiliate Transaction" then
{JOB_GRADE} in "58" to "98&quot and
isnull({TEAM})

I need this to pull everyone where the Team field on their record is null, but it's pulling people who have the Team field populated. Did I choose the wrong function? I tried moving the function to different areas of the selection criteria and it still won't work.

Any help would greatly appreciated...

ChiTownDiva

 

[checkai]

it may be due to your table joins being ='s

dlc

 

[dgillz]

Why is there an "else" clause in your code without an "if" statement at the beginning? Maybe you should post your entire formula.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[synapsevampire]

Also check the Database->Show SQL Query, I doubt that this is passing everything along to the database.

The else is fine, though I might restructure the record selection to assure SQL pass through and improve readability (check my FAQ).

Knowing what's in @mod date would have been useful.

Try:

(
isnull({TEAM})
)
and
(
{ADDRESS_STATE} like {?State}
)
and
(
@mod date} >= PrintDate
)
and
(
left({COMPANY},2) like left({?Company},2)
)
and
(
{PERSON_STATUS} = "A"
)
and
(
{LOCAL_PERS_ID} like {?RC Code/Tracking Unit}
)
and
(
if {?Compliance Report Requested?} like "Antitrust" then
{MGT_LEVEL} like "M"
else
{MGT_LEVEL} like {?Management Level}
)
and
(
if {?Compliance Report Requested?} like "Affiliate Transaction" then
{JOB_GRADE} in "58" to "98"
)

This should work, it's more readable and stands a better chance of passing the SQL.

-k

 

[Ngolem]

Sv - I agree this needs restructuring

{@mod date} >= PrintDate

This definately won't pass to the server because of the formula being used...it will be evaluated on the second pass

if {?Compliance Report Requested?} like "Antitrust" then
{MGT_LEVEL} like "M"
else
{MGT_LEVEL} like {?Management Level}
)
and
(
if {?Compliance Report Requested?} like "Affiliate Transaction" then
{JOB_GRADE} in "58" to "98"
)


I don't like this at all....very confusing. There are 2 conditions for that parameter {?Compliance Report Requested?} ...maybe it works but I would write it differently...perhaps

(
if {?Compliance Report Requested?} like "Affiliate Transaction" then
(
{JOB_GRADE} in "58" to "98" and
{MGT_LEVEL} like {?Management Level}
)
else if {?Compliance Report Requested?} like "Antitrust" then
{MGT_LEVEL} like "M"
else
{MGT_LEVEL} like {?Management Level}
)

perhaps there is no diffenrence or maybe yours is better...not sure.

I agree that

(
isnull({TEAM})
)

Should appear right at the beginning of the formula


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[synapsevampire]

I agree with the printdate problem, they probably meant to use datadate or currentdat.

As for restructuring the record selection, make sure that you test it, it may not get passed in that design, especially in CR 8.5 or below.

-k