I have a report with two sub reports, the main report show the “Mother Batch” and details. And the Sub reports, show totals that were ship to customers.

The first Sub report shows jobs that were dyed and then shipped.
Where as the second report shows jobs that were sent with out being dyed (we’ll call them “raw” batches).

So from a Mother Batch, you can have all dyed or all Raw or a combination of both.

So far, so good. At the bottom of each sub report it Sum’s the total amount shipped.
Then it use this code to sum up both raw and dyed job’s in an unbound field.

=([rptColouredSubReport].[Report]![SumSubColour])+([rptNatSubReport].[Report]![SumNat])

And again this works fine. As long as its a combination of dyed and raw.

But if a job is totally Raw or totally Dyed, in other words there is only data returned in one sub report and the other is empty, the “Grand Total” returns an error. #Error

What can I do with the code/field to return a total in the Grand Total even if there is data in only one of the sub reports?

Perhaps an Iif Statement to check for null values?

I’m not that good with Iif yet, but still trying.

Any help would be great.

Thanks :)

The Nz() function will replace nulls with another value:

= Nz([rptColouredSubReport].[Report]![SumSubColour],0)+ Nz([rptNatSubReport].[Report]![SumNat],0)

Sorry Pat, didn't work. :(

Same #Error

I am running Access '97 here at work, would that make a differance?

Thanks for the help.

You can't reference controls that use aggregate functions. Look up this article, it may help -

208998 - ACC2000 How to Bring a Subtotal from a Subform to a Main Form

Use the HasData method, many examples have been posted here

I must be thicker than a gangster that brings a knife to a gunfight.

I have been trying to get this grand total field in a report going off and on for a week now. :(
And I still can get it to run.
Rich I have seen your posts mentioning that I should search for HasData about fifty times. I have stacks of examples here, but I just don’t seem to be able to get it to work.:confused:

So I am waving a little white flag.

I have created here a simple copy (in all respects very simple) of the data and database that I am working with.
Could some one have a look over it and the report rptTotals and see what I have to do to get the Grand total to display a total weather there is data in two or less subforms.

Any help would be greatly appreciated.
Thanks

:)

Works great, unsure as to what I was doing wrong but its all sorted now.

Download Rich's version to get a good example of how to total subforms weather they hold data or not.

Thanks Rich :)

:)
I can't get this file to open in Access. It keeps telling me I don't have permission to convert this file.

Don't know, sorry, I did not have a problem opening it :confused: