Empty subform breaks Print Preview

[Seph]

Good day again everyone.

Hope all is well .

I have a strange one.

I have a report that shows information from 3 Subforms (linked by Primary key). At the end of the report are some Text Fields running calculations.

This is all working smoothly in Report View (Fields in Red are usually hidden SUM fields):

However, in Print preview mode the Report excludes the 3rd empty subform Costs and the SUM fields break:

Should I be using an IIF command?

Oh and here is the Subtotal calculation field's details:

=[InvoiceJobRSF1].[Report]![LabourTotalSum]+[InvoiceJobRSF2].[Report]![CostTotalSum]+[InvoiceJobRSF3].[Report]![TravelTotalSum]

Thank you in advance.

 

[bob fitz]

Have you tried:
=Nz([InvoiceJobRSF1].[Report]![LabourTotalSum],0)+Nz([InvoiceJobRSF2].[Report]![CostTotalSum],0)+Nz([InvoiceJobRSF3].[Report]![TravelTotalSum],0)

 

[Seph]

Have you tried:
=Nz([InvoiceJobRSF1].[Report]![LabourTotalSum],0)+Nz([InvoiceJobRSF2].[Report]![CostTotalSum],0)+Nz([InvoiceJobRSF3].[Report]![TravelTotalSum],0)

Thanks for your suggestion.

I implemented your solution and then got a new "error" in Print Preview Only

 

[Seph]

Seeing as the calculated fields are working in report view. I don't think its a syntax error. I suspect it might have something to do with the Reports On No Data Event possibly?

 

[Gasman]

So what code is in that event?

 

[Seph]

So what code is in that event?

Currently nothing, just grasping at straws to be honest.

 

[arnelgp]

you can create a query to Calculate those, then you can Dlookup() the value from the query.
if you can share a sample db then it would be easy to explain.

 

[Seph]

you can create a query to Calculate those, then you can Dlookup() the value from the query.
if you can share a sample db then it would be easy to explain.

Thank you for your advice. Isn't there a simpler method of rectifying this rather than creating queries etc?

I mean it's working perfectly fine in Report View and Layout View. Just not in Print Preview.

I can see why Access is confused, the empty subform disappears when Print Preview is selected due to it being technically Null. Thus the calculated fields break.

Can't we implement a syntax of sorts that stipulates ignore missing Field if not present? In this case the Cost Field:

=[InvoiceJobRSF1].[Report]![LabourTotalSum]+[InvoiceJobRSF2].[Report]![CostTotalSum]+[InvoiceJobRSF3].[Report]![TravelTotalSum]

 

[arnelgp]

you can try:

=Val([InvoiceJobRSF1].[Report]![LabourTotalSum] & "") + Val([InvoiceJobRSF2].[Report]![CostTotalSum] & "") + Val([InvoiceJobRSF3].[Report]![TravelTotalSum] & "")

 

[Seph]

you can try:

=Val([InvoiceJobRSF1].[Report]![LabourTotalSum] & "") + Val([InvoiceJobRSF2].[Report]![CostTotalSum] & "") + Val([InvoiceJobRSF3].[Report]![TravelTotalSum] & "")

Appreciate the Idea. Sadly no dice. Report View is perfect but Print Preview isn't happy.

I know it has to do with the missing Subform because when there is records in it, it works:

 

[Gasman]

So could you create a dummy entry 'No data to report'
Are subreports actually needed?, could grouping provide the same info?