Exceeding character limit of control source with multiple subreports

[jimg972]

I am using Access 2007 on Windows XP. I am trying to build a report that contains up to 4 subreports. The main report should add the subtotals of the 4 subreports. Not every report will contain all subreports so I am using IIF to identify subreports with no records. In the main report I can add three IIF routines just fine.



Here is my control source expression that works:

=IIF([srptInvoiceDetail].[Report]![HasData], Nz([srptInvoiceDetail].[Report]![txtSubTaskTotal],0),0) +
IIF([srptAddFLR].[Report]![HasData], Nz([srptAddFLR].[Report]![txtFLRTotal],0),0) +
IIF([srptAddParts].[Report]![HasData], Nz([srptAddParts].[Report]![txtPartsTotal],0),0)

If I add the 4th IIF for the last subreport I exceed the 256 character limit of the expression field.

How can I work around this limitation?

I can envision two ways to accomplish this:

1. Perform the IIF in the subreport to ensure that a valid value is passed to the main report. If the subreport has records, the value would pass through correctly. If the subreport has no records, the HasData flag will reflect that and the value will be set to zero.

2. Perform the IIF calculations in VBA in Report_Open (or some other report event). The IIF calculations would be performed in the Report_Open event and pass the total to the unbound text box in the main report.

Any insight and recommendations would be greatly appreciated.

Thanks,
Jim

[June7]

Have you tested the IIf expression so far? Does the Nz part evaluate if there is no data? The issue with an IIf is that all parts must be able to be evaluated, even if it is not the part that is returning value.

Option 1 should be possible. Option 2 would require a function that could be called by textbox in the report. Can't set Value property of textboxes once report opens.

I have a report where VBA in report Detail_Format event of main form tests if a subreport has records, if not then a label with caption 'No Records' shows.

How is it that not all subreports will be in the output? Or are they all there just maybe with no data?

[jimg972]

Too many late nights

I've been able to get the following expression to work:

=IIF([srptInvoiceDetail].[Report]![HasData], [srptInvoiceDetail].[Report]![txtSubTaskTotal],0) +
IIF([srptAddFLR].[Report]![HasData], [srptAddFLR].[Report]![txtFLRTotal],0) +
IIF([srptAddParts].[Report]![HasData], [srptAddParts].[Report]![txtPartsTotal],0)

Could NOT get the expression with Nz() to work.

>> How is it that not all subreports will be in the output? Or are they all there just maybe with no data?

Good point. All subreports are in the main report. Not all subreports will have records for each invoice. If a subreport has no records, it doesn't display in the report. I have been able to get the subreports to grow and shrink appropriately.

My latest challenge is to comprehend the "no records" case, thus the IIF routine. It would probably be easiest to apply the IIF routine at the subreport level (Option 1). That way, there would always be a value to pass to the main routine. If there are no records, the IIF routine would set the subtotal to 0, otherwise the subtotal of the records for that subreport would be totaled and available to the main routine.

What would the IIF syntax need to be at the subreport level?

For example, my current parts subtotal is:

=Sum([Quantity]*[Price])

This expression is in the text control box txtPartsTotal in the InvoiceID footer.

I know I can't use the following IFF function because I would lose my summation:
=IIF([srptAddParts].[Report]![HasData], [srptAddParts].[Report]![txtPartsTotal],0)

The following IFF results in a #Name? error:
=IIF([srptAddParts].[Report]![HasData], Sum([Quantity]*[Price]),0)

I THINK I'm on the right track but haven't been able to nail down the syntax.


Regarding Option 2 (VBA code), what would the syntax look like for the function call in the text box of the main report? Since I'm dealing with currency I assume I would be working with doubles in the VBA code.

[June7]

Good, the IIf terms of the expression work. Try this before we get caught up in some elaborate coding. Put each of the 4 terms into separate textboxes on the main form (they can be not visible). Then in a 5th textbox add the other 4.

[jimg972]

Clever approach. It really helps to break down the problem into managable chuncks. When I add the 4 text boxes I can see that the IIF function is not performing as expected. If one or more of the subreports has no records I am expecting a 0 to be returned. Instead, I'm getting #Error.

I figured out that the following syntax results in #Error:
IIF([srptAddParts].[Report]![HasData], [srptAddParts].[Report]![txtPartsTotal],0)

It works properly if I change it to:
IIF([srptAddParts].[Report].[HasData], [srptAddParts].[Report].[txtPartsTotal],0)

This gets me past my biggest hurdle. Thank you very much for the insight.

[June7]

Glad you found the fix. I should have noticed the ! marks. I use ! when referring to fields of Recordset, the dot when referring to controls or properties. The dot will provoke intellisense popups.