Crystal interprets Null as "0"

[BobWman]

Crystal Reports is not interpretting the value of fields correctly. The report runs from a SQL Server view, and the view displays Null values for the “MonthExecuted” when it is run alone. But within Crystal Reports, the value is a string zero - "0".

This is the portion of the view that returns the field (it converts a date to “YYYYMM” format):

MonthExecuted = Convert(CHAR(4),Year(J.JPAExecuted)) + Right('0'+
Ltrim(Rtrim(Convert(Char(2),Month(J.JPAExecuted)))),2),

I have used the Isnull function in SQL Server to send over various Non-Null values, but whatever I do, the report interprets the value as a string zero.

This is the part of the view that now creates '999999' if the JPAExecuted Field is null and you run the view in query analyzer. But if you browse the field values in Crystal, all you see is '0', no matter what value the view creates.

MonthExecuted = Convert(Varchar(6),
IsNull(Convert(CHAR(4),Year(J.JPAExecuted)) + Right('0'+
Ltrim(Rtrim(Convert(Char(2),Month(J.JPAExecuted)))),2),'999999')
),

I have verified the value with the following formula in CR:

If {vJPAMonthlyManagementReport.MonthExecuted} = '0'
Then '777'

This prints the value ‘777’ for months that should return Null (or ‘999999’).

Does anyone know what is happening?

Thanks.

 

[BobWman]

Found the problem.

Thanks for checking.