Solved Missing calculated fields (1 Viewer)

[Seph]

Good day everyone,

I have a Text Box at the bottom of my report called Subtotal, which acts as a calculated field:

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

InvoiceJobRSF1, InvoiceJobRSF2 & InvoiceJobRSF3 are Subforms on the report that Subtotal references in its calculations.

It works well, but here's the catch.

Not all reports have values in each subform as some subforms references items not billed to the client.

So when a Subform is "null" the calculation gives a #type error.

Which is most likely due to one missing filed in the SUM used in Subtotal.

Is there a way (perhaps an IIF command) that will essentially "ignore" the missing Subform so the calculated field doesn't break?

Thanks!

 

[theDBguy]

Hi. Try wrapping each value inside the Nz() function.

 

[Seph]

Hi. Try wrapping each value inside the Nz() function.

Thanks for the idea. I tried that.

The physical subform (containing no data) disappears when Report view is switch to Print preview.

So for example, InvoiceJobRSF3 will disappear and then the calculated field can't pin down the data and essentially breaks.

The Nz function wont Null a "field" that essentially doesn't exist.

I'm hoping there is a method for calculated fields to overlook missing fields or replace them.

 

[theDBguy]

Thanks for the idea. I tried that.

The physical subform (containing no data) disappears when Report view is switch to Print preview.

So for example, InvoiceJobRSF3 will disappear and then the calculated field can't pin down the data and essentially breaks.

The Nz function wont Null a "field" that essentially doesn't exist.

I'm hoping there is a method for calculated fields to overlook missing fields or replace them.

Since I can't see your report, why would the subform disappear? Why can't it stay and show as empty? Are you able to share a sample copy of your db to demonstrate the problem? You may end up using some code to produce the total instead.

 

[Seph]

Since I can't see your report, why would the subform disappear? Why can't it stay and show as empty? Are you able to share a sample copy of your db to demonstrate the problem? You may end up using some code to produce the total instead.

The 3 subforms on the report and linked via the primary key, TicketNumber, of the Invoice table.

As such, when there are records in each of the tables that reference the TicketNumber they populate data.

This all works perfectly fine in Report View (Fields in red are Invisible SUM fields)

As you can see the SUM field for Costs which has a Nz function around it works.

However if you switch to Print Preview, the Subform disappears:

I suspect this is due to the Subform having no data to populate. So wrapping the Subtotal in a Nz function doesn't work in this instance.

I can try create a sample .db for you and upload it

 

[Gasman]

Is this post a continuation of this one?

Empty subform breaks Print Preview

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...

www.access-programmers.co.uk

 

[Seph]

Is this post a continuation of this one?

Empty subform breaks Print Preview

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...

www.access-programmers.co.uk

Correct