Avatar of btgtech
btgtech
Flag 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.
Microsoft Access

Avatar of undefined
Last Comment
Ryan

8/22/2022 - Mon
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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Ryan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.