asked on

Access 2010 Report - #Size! Error

We have developed an Access 2010 report which has the following parts:

Subreport a - with a calculated Total which is held in an unbound field on the report - if this subreport does not have data, then we do not show it

Subreport b - with a calculated Total which is held in an unbound field on the report - if this subreport does not have data, then we do not show it

Grand Total on Main Report = VAL(Nz( Total from Subreport a, 0)) + Val(Nz(Total from Subreport b,0))

When we run the report we receive the #Size! error for the subreport which does not have data and is not shown

How do we resolve this? The report has been running fine previously, but we cannot fine anything that has changed to impact this.

Ryan

I believe this is caused by no records being returned in the subform.

So just catch the error and return 0. You can remove spaces, I've just done it to be able to see what is done.

Grand Total on Main Report =
iferror(
VAL(Nz( Total from Subreport a, 0)) + Val(Nz(Total from Subreport b,0)),
0,
VAL(Nz( Total from Subreport a, 0)) + Val(Nz(Total from Subreport b,0))
)

Ryan

You can also use another textbox as a placeholder. Make it Visible=false with formula = VAL(Nz( Total from Subreport a, 0)) + Val(Nz(Total from Subreport b,0))

Then reference that textbox in your visible one.

Grand Total on Main Report = if(isnumber(Text1),text1,0)

btgtech

ASKER

but Subreport a may have a value - let's say 10000 and subreport b may have no value.

the no value in subreport b causes the error.

so if there is an error, we do not want the total to be 0 we would want the total to be 10000.

thoughts?

ASKER CERTIFIED SOLUTION

Ryan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial