Criteria for Subreport

[blazerboy6]

I've got multiple subreports built into a quarterly main report which are all sums. The main and subreports work great except one little catch, if the value is Null for the sum that quarter, the sub report is blank.

I've been trying to figure out how to write criteria/code into the Control Source or Subreport to give me a return of "0" for a Null value, but no luck. Any suggestions?

[kennejd]

Change the data in the report's query to include zero values.

[blazerboy6]

The issue I'm having is the fields in the table are:

rmMatrixID, rmDate, rmNumberOfBrochuresDistributed.

There are no zero values in the data, only inputs for brochures distributed at events. My issue is that if no events are conducted during a quarter, I want the Main report to show a zero. I've tried an NZ(), but there is no record within that quarter, so there is no actual Null value to return a "0" for.

My subreport is a single box based on the Query, which organizes the values by fiscal year and quarter (based on rmDate), then summing the totals of each quarter and presenting that as SumOfBrochuresDistributed in the text box of the subreport, which is just a row in the Main Report. Everything works great except for a blank vs. "0" in the main report (organized by fiscal year and quarter)

Is there a formula, iif statement, SQL, VBA, or anything I can add/change besides entering a zero value for a specific date within the data? That would obviously fix the problem of not having a record, but that seems so defeatist in the effort to automate and simplify.

[blazerboy6]

Bumping.

I've shelved this issue for a while now and just dealt with the blanks of the report, but each quarter it still bugs me. Does anyone have a suggestion as to a filter in the subreport, or a change in the Main Report?

[blazerboy6]

Sort of solved:

Added in a unbound text box with

=IIf([rptBrochuresSubreport].[Report].[HasData],Null,"0")

in it's Control Source. I overlaid this onto the subreport (have to turn off "Stacked Layout") so that if the subreport field is blank, a "0" pops up in it's place. not perfect, but at least it looks better...