Return Null as Zero

[bbshriver]

Ok, here's something that's driving me nuts, I've searched high and low on the web, and nothing seems to work.

I'm trying to create a report to track the number of days spent on a project. Each portion of the project is recorded as an event with a start date and end date, and whoever is responsible for that particular portion of the project.

I'm trying to break down the report into time spent by each individual responsible. On the report I have a text box with the code
"=Sum(DateDiff("d",[Start Date],[End Date]))*5/7"

(the 5/7 is to estimate working days rather than total days since I also couldn't figure out a "networkday" function like Excel has) Other than that the code uses a query as the source.. The query already has the group responsible inserted, so I just want to add up the total number of days reported under that individual.

The code works perfectly, however for individuals that don't have any events, it returns #Error. I'm assuming because the query returns a Null value for them.

I've tried using

IIf(Sum(DateDiff("d",[Start Date],[End Date]))*5/7>0,=Sum(DateDiff("d",[Start Date],[End Date]))*5/7,"0")

IIf(IsNull([End Date]),"0",Sum(DateDiff("d",[Start Date],[End Date]))*5/7)

and

=Sum(DateDiff("d",Nz([Start Date],0),Nz([End Date],0)))*5/7

None of which have worked, all still return #Error.

What am I missing?

[slave138]

You might want to try something like:

=IIf(IsNull([Start Date]) Or IsNull([End Date]),0,Sum(DateDiff("d",[Start Date],[End Date]))*5/7)

That way if either field is null it will still give a 0 result. I also opted to keep it a number instead of making it a string in case it is used in another calculation.

The formula worked on null values but gave me the same result for each record (except the null ones) in the test DB I created. Once I removed the SUM function from the string I received individual values for each record. Not sure if that's your intent...

=IIf(IsNull([Start Date]) Or IsNull([End Date]),0,DateDiff("d",[Start Date],[End Date])*5/7)

[slave138]

Here's the sample DB if you're interested (I have the report showing both the formula with and without SUM)

[bbshriver]

Still gives me #error...

Seems like there's something wrong on my database

[slave138]

Upload a copy (remove personal info first) of your DB and we might be able to help find the problem.

[bbshriver]

My file seems to be 4.5MB which is too large to upload here..

[slave138]

Compressed DB and zipped?

[bbshriver]

Here ya go

[slave138]

How do I recreate your scenario?

[bbshriver]

There is a form labeled "generate report", go to that form, select "1" and click the generate button.

You will see correct values for the times that have been entered and #error for the ones that are null.

The main report uses several sub-reports based on individual querries that are triggered from the generate report form.

[slave138]

I'll admit I'm having a hard time tracing the data through all of the nested queries and subreports, but there isn't any indication of an error until the report tries to pull the data from the subreport. Is there a specific reason you are using subreports instead of just pulling the data directly to the report?

[bbshriver]

For me, if I run any sub-report by itself it still returns #error. The main report only displays the calculated value from the sub, so there should be little opportunity for error there.

The reason for the individual sub reports is trying to individually tally all of the "responsible" categories. I was not able to do that effectively without using sub reports

[slave138]

I'll try to take a fresh look at it when I'm not at work - but nothing is making sense so far:

I added the formula I suggested earlier to the fields on the subforms (none seemed to have it) but they are still coming up with #Error on the main report - as you described. On the subreports, they displayed a 0 when I ran them directly.

Then when I changed the code to output a 1 if either field was null instead of 0, it still gave a 0 on the subreports.

Then I replaced the functions on the subreports with a constant value (=2) and it still displayed an #Error on the main report but displayed the entered value on the subreport.

Just for fun, I called some MsgBox's from the main report referencing a field with the formula and one with a constant. Both displayed the same as they did on the subreport.