Using subreport result in expression

[Heather]

I have a report that compares the contract value of a
number of projects with the sum of the transactions for
each project and calculates the contract amount remaining.
The report is based on the contract table and the
transactions are brought in with a subreport on the
transactions table. My problem comes when there is a
contract with no transactions yet. The transactions
subreport is blank for that project, the expression
results in #Error and I can't find any way of getting
round the error. Current expression: [SumofContractValue] -
[srptContract].[Report]![SumofTransValue]
I have tried IsNull for the subreport but of course the
actual subreport is never null, it just doen't have a line
for that project.
Can anyone help?

 

[Allen Browne]

Yes, if the subreport has no records, nothing shows at all. Referring to the
non-existent text box in the subreport then yields error.

To avoid that, test the HasData property of the report in the subreport:
= [SumofContractValue] - IIf([srptContract].[Report].[HasData],
Nz([srptContract].[Report]![SumofTransValue], 0), 0)

 

[Heather]

Excellent!
Thank you

-----Original Message-----
Yes, if the subreport has no records, nothing shows at all. Referring to the
non-existent text box in the subreport then yields error.

To avoid that, test the HasData property of the report in the subreport:
= [SumofContractValue] - IIf([srptContract].[Report]. [HasData],
Nz([srptContract].[Report]![SumofTransValue], 0), 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that compares the contract value of a
number of projects with the sum of the transactions for
each project and calculates the contract amount remaining.
The report is based on the contract table and the
transactions are brought in with a subreport on the
transactions table. My problem comes when there is a
contract with no transactions yet. The transactions
subreport is blank for that project, the expression
results in #Error and I can't find any way of getting
round the error. Current expression: [SumofContractValue] -
[srptContract].[Report]![SumofTransValue]
I have tried IsNull for the subreport but of course the
actual subreport is never null, it just doen't have a line
for that project.
Can anyone help?

.