Need isnull within select statement

[littleress]

I have a statement where currently it pulls a list of payments that were pulled that day, but I need it also to make sure that a certain payment id is null.

Currently:
{MPT.CLUB_ID} = {?Club_ID} and
{MPT.TRANSACTION_DATE} >= {?begin_date_range} and
{MPT.TRANSACTION_DATE} <= {?end_date_range} and
{MPT.PRODUCT_ID} = 9000 and
{MPT.STATUS} = "C" and
{MPT.RECEIVED_AT_CLUB} = "Y"

And I need something added that says isnull({MPT.INSTANT_PAY_ID})
But everytime I add it - it loses the data. So can anyone see what I might be doing wrong that it won't take and isnull field??

 

[lbass]

isnull({MPT.INSTANT_PAY_ID}) and
{MPT.CLUB_ID} = {?Club_ID} and
{MPT.TRANSACTION_DATE} >= {?begin_date_range} and
{MPT.TRANSACTION_DATE} <= {?end_date_range} and
{MPT.PRODUCT_ID} = 9000 and
{MPT.STATUS} = "C" and
{MPT.RECEIVED_AT_CLUB} = "Y"

However, are you sure the other fields are populated when the PayId is null? I think you shold lay out your fields in the detail section and see what values other fields have when PayID is null.

-LB

 

[Madawc]

If it loses the data, then maybe the field is always null. Try doing a boolian formula field,

isnull({MPT.INSTANT_PAY_ID})

Don't select, but display it with the data. If it always says 'True', then the computer is doing just what you asked and the data is the problem.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[littleress]

Thank you. I will try this. My biggest dilemna is that if I take that exact query and run it in Toad, it works fine. Some days I think Crystal tries to make me crazy!!! I appreciate your replies.

 

[lbass]

In Crystal, null checks in a formula must always precede any reference to the potentially null field--so a good practice is to place the null checks first.

-LB

 

[kenhamady]

If the NULL criteria works in TOAD but not in Crystal then it could be that your report options are set to convert the nulls to the default string "". Maybe you don't have any NULL values in the report even though they are NULL in the database.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[Madawc]

A sensible test would be

isnull({MPT.INSTANT_PAY_ID})
or
{MPT.INSTANT_PAY_ID} = ""

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10