How to make Null values appear as 0?

[pokerace]

C10
ODBC DB.

I am trying to create a report that makes the null values appear as 0. Right now it jsut skips them completely. I tried checking the 'convert null values to 0' in report options, but that didn't work. Is there another way to go about this?

 

[Knoxville]

Are you actually getting records with Null values returned? If not then you may have to change your selection criteria. If you are getting the records with null values back, how about a formula field?

IF IsNull({fieldname})
THEN 0
ELSE {fieldname}

 

[GJParker]

There is no option 'convert null values to 0'

the option is 'Convert NULL Values to default' so only fields with a datatype of number or currency will be converted to zero, where as string fields will be converted to ""

If your field is a string then you will need a formula like

If IsNUll({MyField}) Then
"0"
Else
{MyField"

HTH


Gary Parker
MIS Data Analyst
Manchester, England

 

[pokerace]

Actually, I guess they are no Null fields. They are fields that don't exists.

I am tryto to group a string field (3 different outcomes) by year and then summerize the string fields.

For example, I want the total of each of the outcomes to should up for 2002, 2003 and 2004 so that I have

2002
Outcome 1 Total = 10
Outcome 2 Total = 7
Outcome 3 Total = 5

2003
Outcome 1 Total = 0
Outcome 2 Total = 7
Outcome 3 Total = 5

2004
Outcome 1 Total = 10
Outcome 2 Total = 7
Outcome 3 Total = 5

The problem is that there are no items assocated with Outcome 1 in the database for 2003, and so it just skips that listing, but I woul dlike it to say 0. Is that possible?

 

[chelseatech]

So what you are trying to do is display some data values that don't exist in your report.

If there are only 3 outcomes for each year, then use three if then else formulas, and summarise those for each year. You will not then need the group for Outcome. The formulas would look like
If {table.outcome}="1" then 1 else 0

If you want to add a field value instead of just counting, then use that field instead of the one.

Download the back issues of my newsletter to see other ways to report off data that doesn't exist in the database.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm