Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with 2 NULL values

Status
Not open for further replies.

nikol

Programmer
Apr 12, 2005
126
US
Hi,
How can I get the TOTAL for Null Values.
UId CHILDREN OTHER TOTAl(SUM (UID)+(CHILDREN)+(OTHER) --- --------- ----- -------------------------------
5 7 12
1 6 7
4 2 1 7
6 6
4 2 8 14
6 1 7 14
 
Hi,
How can NULLs have values to total?

Please explain further..






[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
In Ist case I had only 1 null value like
UId CHILDREN OTHER @FinalTotal 5 7 12
2 1 4 7

I made a formula @finaltotal
(if isnull ({children}) then
({uid})+ ({other})
else {@Total})

where @Total is another formula
UID + CHILDREN + OTHER
Which is working giving me the sum of all three columns inspite of null value.

Now I have 2 null values in a row Like
UID CHILDREN OTHER
5
4 6 10
How Can I find the sum of a row if it has null values. I tried
If ISNULL giving me Errors..
 
Hi,
Still confused..Why use 2 formulas?

Doesn't this work, just add up the Total:
@total
Code:
UID + CHILDREN + OTHER
5                  7        //should = 12
3        4         2        // " = 9

You may have to use the 'convert database null values to default' option ( will make Null show as 0 )




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
If you're adding three fields, any of which may be null, the simplest solution is to have a separate formula field for each. Thus @Children says
Code:
(if isnull ({children}) then 0 else {children}
Then add up all three formula fields, which will be zero when they were null on the database.

Having come to Crystal from mainframe languages, I got a 'cultural shock' when encountering null. It means 'no data': Mainframe languages mostly treat this as the same as zero.
It is actually a finer shade of meaning, the difference between 'Yes, we have no bananas' and 'I don't know how many bananas we have, it could be some, it could be zero'. In Crystal, the entry is 0 or null and can be tested for.
Note that Crystal assumes that anything with a null means that the field should not display.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
Hi,
Actually NULL has several meanings depending on the database/application..

In Oracle it means 'indeterminable' - not 0, not blank, but unknown and unknowable, so it cannot be used in any standard comparison - only IS or IS NOT can be used.



[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top