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
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.
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))
)