Link to home
Create AccountLog in
Avatar of btgtech
btgtechFlag for United States of America

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.
Avatar of Ryan
Ryan
Flag of United States of America image

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))
  )
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)
Avatar of 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
Avatar of Ryan
Ryan
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account