#error when adding subreport field with main report field

[Andy C]

Hi,

I am trying to generate a report that gives me daily
totals of sales. The data comes from 2 tables. However, if
on a given day there are no sales in 1 table and some in
the other, when I try to calculate a total in a report I
get a #error message in the calculated field..This is
because I am attempting to add a value in one field to no
value in the other field.
Is there a way around this such as the IIF function to
show at least the value of one field if there is a Null
value in the other?

your help would be much appreciated,
thanks,
Andy

 

[Allen Browne]

Test the HasData property of the report in the subreport control. If there
is no data, the subreport value is not null: it doesn't exist (so referrring
to it generates the error).

The Control Source of your text box will be something like this:
=IIf([MySub].[Report].[HasData], Nz([MySub].[Report]![MyTotal], 0), 0)

Replace "MySub" with the name of your subreport control, and "MyTotal" with
the name of your text box in the subreport.