CR 8.5, How do I test for NULL?

[Former Member]

I want if ACJEITM_AmtCr is NULL, I want this to dispay ACJEITM_Des otherwise I want PTPUR_ReceiveRef to display

Is this = "" giving me the same as NULL?

IF {CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr} = "" then

{CIT_AC_DEBCRD_MEMO;1.ACJEITM_Des}

ELSE {CIT_AC_DEBCRD_MEMO;1.PTPUR_ReceiveRef}

END IF

Any help would be appreciated

Kelvin

[former_member292966 · Accepted solution]

Hi Kelvin,

Before we look at the report, we need to see how Crystal is handling NULLs. Open the report and go to File | Report Options. If the option for Convert NULL Values to Default Value is checked then you can use the formula above.

If the option is not checked then you will need to use:

IF ISNULL ({CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr}) then

{CIT_AC_DEBCRD_MEMO;1.ACJEITM_Des}

ELSE {CIT_AC_DEBCRD_MEMO;1.PTPUR_ReceiveRef}

END IF

Personally I have the option checked. It makes handling nulls a bit more tolerable in most situations.

Good luck,

Brian

[Former Member · Accepted solution]

thanks for the reply...

I checked the box...

Why does it go to the end of the ELSE line and say "The remaining text does not appear to be part of the formula"? This is with Crystal Syntax

If I put it in Basic Syntax I get "A statement is expected" at the end of the first line, If isnull...

Thanks

Kelvin

[Former Member · Accepted solution]

Hello,

What is this field holds? I mean what data type, string? or number? int? because ({CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr}) ="" could be related to a string value and if your data type is number or int then it might not work under CR.

Did you try the suggestion given above like ISNULL??

Alternatively you can use "Hasvalue" like this

If not(hasvalue(fieldname) .......

providing you are using CR 2008. This hasvalue does not work in CR XI though.

Let us know if any of the suggestion worked.

Regards

Jehanzeb

[Former Member · Accepted solution]

This is what I'm using now.

AmtCr is a number (Double)

Des is text (varchar)

ReceiveRef is text (varchar)

IF ISNULL ({CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr}) THEN

{CIT_AC_DEBCRD_MEMO;1.ACJEITM_Des}

ELSE {CIT_AC_DEBCRD_MEMO;1.PTPUR_ReceiveRef}

END IF

I can't see why this doesn't work...

I'm using CR 8.5, this is dictated by our business system vendor whose system I am making the report for.

Thanks for your time!

Kelvin

[Former Member · Accepted solution]

Hi Kelvin

Please change the syntax to Crystal and try the following formula :

IF ISNULL({CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr}) or {CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr} = " " THEN

{CIT_AC_DEBCRD_MEMO;1.ACJEITM_Des}

ELSE

{CIT_AC_DEBCRD_MEMO;1.PTPUR_ReceiveRef}

Thanks,

Sastry

[Former Member · Accepted solution]

Thanks for your reply.

I entered your formula and I get a message back saying "A number is required here" and it jumps to the second line where I put an X.

IF ISNULL({CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr}) or {CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr} = X" " THEN{CIT_AC_DEBCRD_MEMO;1.ACJEITM_Des}ELSE {CIT_AC_DEBCRD_MEMO;1.PTPUR_ReceiveRef}

Do you know what it's fussing about?

I'm using CR 8.5 if it matters...

Thanks

Kelvin

[former_member260594 · Accepted solution]

Kelvin,

If the string null values have been converted to default then they will be equal to an empty string "". Placing the X in front is saying that the string will be equal to X"" which is incorrect in Crystal Syntax. What do you need the X for? If you just want to check for null then remove the X.

[Former Member · Accepted solution]

Hi Kelvin

Sorry, I am checking with space insted of 0. Now please try this

IF ISNULL({CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr}) or {CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr} = 0 THEN{CIT_AC_DEBCRD_MEMO;1.ACJEITM_Des}ELSE {CIT_AC_DEBCRD_MEMO;1.PTPUR_ReceiveRef}

Thanks,

Sastry

[former_member292966 · Accepted solution]

Hi Kelvin,

The error you are getting is because " are for a string and not numbers.

Do this instead:

IF ISNULL({CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr}) or {CIT_AC_DEBCRD_MEMO;1.ACJEITM_AmtCr} = 0 THEN{CIT_AC_DEBCRD_MEMO;1.ACJEITM_Des}ELSE {CIT_AC_DEBCRD_MEMO;1.PTPUR_ReceiveRef};

This will get you around the problem. Since AmtCr is a number, you need to check the value for a NULL or for 0. " " is a space which is a string.

Hope this helps,

Brian