Blank records in SubReport to Total in Main Report

[Karin]

I have created a subreport which has account detail for all departments. The
account detail is summed by category within the subreport. I am bringing
these forward by using control fields in the subreport and it works great.
The issue is that I am trying to use the results in a calculation to subtract
them from the department total in my main report. It works fine as long as
there are records with data. If I have a department that has no detail lines
I get an #Error in the fields and total field.

I have tried using Nz
=Nz([ExpAllocRemQuery subreport].[Report]![Text33],0)
in both the sub report and the main report fields but it seems like it will
no work becuase there are no records chosen from my criteria. I also tried
using format with 0;(0);0;0 to see if I could force a zero but that does not
seem to work either.

Does anyone have any idea how I can handle this?

Thanks in advance,

Karin

 

[Ken Sheridan]

Karin:

Try calling the IsError function to trap the error and return a zero:

=IIf(IsError([ExpAllocRemQuery
subreport].[Report]![Text33]),0,[ExpAllocRemQuery
subreport].[Report]![Text33])

Ken Sheridan
Stafford, England

 

[Karin]

Thanks Ken! I had to tweak one other thing to get it to work but you pointed
me in the right direction. In the first part of the expression I had to use
my original sum field instead of the control field to search for the errors
to make it work. I really appreciate your help!

Karin

Karin:

Try calling the IsError function to trap the error and return a zero:

=IIf(IsError([ExpAllocRemQuery
subreport].[Report]![Text33]),0,[ExpAllocRemQuery
subreport].[Report]![Text33])

Ken Sheridan
Stafford, England

I have created a subreport which has account detail for all departments. The
account detail is summed by category within the subreport. I am bringing
these forward by using control fields in the subreport and it works great.
The issue is that I am trying to use the results in a calculation to subtract
them from the department total in my main report. It works fine as long as
there are records with data. If I have a department that has no detail lines
I get an #Error in the fields and total field.

I have tried using Nz
=Nz([ExpAllocRemQuery subreport].[Report]![Text33],0)
in both the sub report and the main report fields but it seems like it will
no work becuase there are no records chosen from my criteria. I also tried
using format with 0;(0);0;0 to see if I could force a zero but that does not
seem to work either.

Does anyone have any idea how I can handle this?

Thanks in advance,

Karin