Subreport's sums problems

[Sanasta]

I have a report with 5 subreports. Each subreport's footer contains sum of spesific expenses. In main report I
have summed these sums. In case any subrepost is missing (e.g. we have not DKV expenses), my total (final) sum is error. I have tried with NZ, IsError, Is Null, IsEmpty functions, but result is the same.
How to avoid this problem?
Any help shall be greatly appreciated
Sanasta

 

[hneal98]

WHat formula have you used to sum up the sums?

 

[Sanasta]

Thanks
The way I have proceeded is the following:
I have 2 ways of filling petrol: DKV and Cash and have 2 different tables, 2 different queries and subreports.
In main report I have 2 subreports with all details re filling. In subreport’s footer I have sum of litters filled. Control’s names are LitDKV in one subreport and LitCash in another.
In the main report control source is:

=[qryZarejdaneDKV].[Report]![LitDKV]+[qryZarejdaneCash].[Report]![LitCash]

The result is OK if we had filled both DKV and cash, but if one is missing Error appears. I suppose that when there is not any petrol fillings corresponding subreport is missing and function NZ can’t help. May be this could be arranged with procedure but I can’t consider how. May be my qualification is not enough.
Shall appreciate if you could help me.
Thanks in advance

 

[gearhead03]

Try setting your defaults to zero. Under properties for litDKV and litCash make sure the default is set to zero. That way your formula always has a figure to work with. Good luck

 

[Sanasta]

Thanks, but the litDKV and litCash are calculated test boxes and they have not drfault property at all.

 

[hneal98]

As gearhead03 mentioned, you need to get zero's in the fields if they are going to be null. While the text boxes do not have default properties, you can still fill them with zero's. How do you currently populate them? If they are populated from the table, you can make sure the table's default property is set to zero.

Let me know if you need more info.

 

[dhookom]

No amount of defaults or NZ() will fix the issue. Try the following expression:
=IIf([qryZarejdaneDKV].[Report].HasData,[qryZarejdaneDKV].[Report]![LitDKV],0) + IIf([qryZarejdaneCash].[Report].HasData, [qryZarejdaneCash].[Report]![LitCash],0)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Sanasta]

Dear Duane,
The result of your proposal gave perfect results.
I can't exspress my gratitude with words.
Thanks
Sanasta