ISNull Help Required

[PETEHO]

I have managed to confuse my self all ends up and just need a helping hand.

I have the following selection

{nlforcm.budget_code} = {?Budget Code} and
not ({@YTDActual} <> 0) and {@YTDBudg} <> 0.

What I require is to do an inull at the end of this selection, when the results of the selection have been processed. If someone could please advise it would be appreciated

Pete

 

[GJParker]

What are you trying to test for a null value ? Ideally Null values should be the first test in the selection formula.

Please explain a little more about your selection formula

{nlforcm.budget_code} = {?Budget Code} and
not ({@YTDActual} <> 0) and {@YTDBudg} <> 0.

is the same as

{nlforcm.budget_code} = {?Budget Code} and
{@YTDActual} = 0 and {@YTDBudg} <> 0

the 2nd would be more efficient.

Gary


Gary Parker
MIS Data Analyst
Manchester, England

 

[Madawc]

When writing formulas, tests for null must always come first. Without isnull, Crystal will stop processing the formula when it hits a null value; the default assumption being that null means show nothing. I made this mistake several times while I was learning Crystal, because it's not obvious to someone who's used to other programming languages.

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

 

[GJParker]

tests for null must always come first

Madawc this only applies when testing for multiple values of a specific field, then the test for Null values must always be done first i.e.

IsNull(Table.FieldA} or {Table.FieldA} <> 0

but this is also okay

{Table.FieldA} = 100 and IsNull({Table.FieldB})

HTH




Gary Parker
MIS Data Analyst
Manchester, England

 

[PETEHO]

Gary

My ISNULL is supposed to bring back the results of two tables called Nlmastm and NLFORCM

There will always be records in NLMASTm so I have my left outer join on.

What i was trying to do was ISNull({nlforcm.nominal_code}) "and/or" {nlforcm.budget_code} = {?Budget Code} and ({@YTDActual} <> 0) and {@YTDBudg} <> 0.



I need to do the budget code parameter selection and to eliminate records where there are zero movements for the year. Then once those results are returned. I need to do get the records that have actual movements for year but they had no budget set in the NLFORCM table. the ISNULL is required after all the selections are done.

 

[GJParker]

Assuming the {@YTDActual} and {@YTDBudget} calculations only reference fields from NLMAST then try something like this.

({@YTDActual} <> 0 and {@YTDBudg} <> 0) and
IsNull({nlforcm.budget_code}) or {nlforcm.budget_code} = {?Budget Code}

If this doesn't work then please provide a sample of your input data and expected outcome, also include the 2 referenced formulas along with technical specifications such as crystal version and db etc.

HTH


Gary Parker
MIS Data Analyst
Manchester, England