Simple addition formula not working, Null problem?

[johndill]

Hi,
I am creating a report from a single Access table. There are 2 fields in the table ([Total New Hours]and [TotalOldHours]) that I want to add. Some of the records have nulls so I accounted for them by using IsNull:

If IsNull({ProjDump1.Total New Hours}) Then
0
Else

{ProjDump1.Total New Hours}

and

If IsNull({ProjDump1.TotalOldHours}) Then
0
Else
{ProjDump1.TotalOldHours}

The records then either retun a number or a 0 to the report.

When I try to sum those 2 fields by creating a formula:

{ProjDump1.TotalOldHours} + {ProjDump1.Total New Hours}

The report only retuns a correct value if both fields are numbers. If one of the fields is '0' the Total field remains blank.

Any ideas?

 

[synapsevampire]

It appears that you created these null checking fields in Access, not in CR.

I'm not sure if there are bugs associated with this in Access, but it shouldn't matter, just create 2 formulas within CR to handle this, the syntax is identical:

//@NewHrs
If IsNull({ProjDump1.Total New Hours}) Then
0
Else
{ProjDump1.Total New Hours}

//@OldHrs
If IsNull({ProjDump1.TotalOldHours}) Then
0
Else
{ProjDump1.TotalOldHours}

Now reference the 2 formulas in another formula to add them:

@NewHrs+@OldHrs

Another option is to select File->Report Options->Convert Null Value to Default to change null values to zero (and default all other nullable field types) throughout the report. This will prevent you from keying off of nulls though, so make sure that you've no tests for null required for other fields.

-k