In a report I have a subreport. It works great when the subreport has 
records to display. When there are no records to display the total on the 
subreport is blank.  Displaying the subreport by itself based on an empty 
table shows the total as "error". The source of the control being summed 
is a field in the underlying table.

nz() does not make any difference, I have even tried "=IIf(Count(*)
=0,0,Sum(nz([amount])))". They all work except when there are no records 
to display. I do not have the same problem in a very similar form where NZ
() provides a value when there are no records. The failure is only in a 
printed report.

I will really appreciate help with this 

David

probably the easiest is to create a totals control called txtTotal and set
its visibilility to false. Then add an on format event

if sum([amount]) > 0 then
me.txtTotal.visible = true
else
me.txtTotal.visible = false
end if

Steve K


-----Original Message-----
From: luckychap@b... [mailto:luckychap@b...]
Sent: 27 January 2003 03:52
To: Access
Subject: [access] Error in printed total when no records


In a report I have a subreport. It works great when the subreport has
records to display. When there are no records to display the total on the
subreport is blank.  Displaying the subreport by itself based on an empty
table shows the total as "error". The source of the control being summed
is a field in the underlying table.

nz() does not make any difference, I have even tried "=IIf(Count(*)
=0,0,Sum(nz([amount])))". They all work except when there are no records
to display. I do not have the same problem in a very similar form where NZ
() provides a value when there are no records. The failure is only in a
printed report.

I will really appreciate help with this

David

> probably the easiest is to create a totals control called txtTotal and 
set
its visibilility to false. Then add an on format event

if sum([amount]) > 0 then
me.txtTotal.visible = true
else
me.txtTotal.visible = false
end if

Steve K


-----Original Message-----
From: luckychap@b... [mailto:luckychap@b...]
Sent: 27 January 2003 03:52
To: Access
Subject: [access] Error in printed total when no records


In a report I have a subreport. It works great when the subreport has
records to display. When there are no records to display the total on the
subreport is blank.  Displaying the subreport by itself based on an empty
table shows the total as "error". The source of the control being summed
is a field in the underlying table.

nz() does not make any difference, I have even tried "=IIf(Count(*)
=0,0,Sum(nz([amount])))". They all work except when there are no records
to display. I do not have the same problem in a very similar form where NZ
() provides a value when there are no records. The failure is only in a
printed report.

I will really appreciate help with this

David

David,
Have you tried checking for null values?
example:

=IIf(IsNull([Quantity]),0,[Unit_Cost]*[Quantity])

Good Luck,
Derrick Flores
San Antonio, Texas

----- Original Message -----
From: <luckychap@b...>
To: "Access" <access@p...>
Sent: Monday, January 27, 2003 3:51 AM
Subject: [access] Error in printed total when no records


> In a report I have a subreport. It works great when the subreport has
> records to display. When there are no records to display the total on the
> subreport is blank.  Displaying the subreport by itself based on an empty
> table shows the total as "error". The source of the control being summed
> is a field in the underlying table.
>
> nz() does not make any difference, I have even tried "=IIf(Count(*)
> =0,0,Sum(nz([amount])))". They all work except when there are no records
> to display. I do not have the same problem in a very similar form where NZ
> () provides a value when there are no records. The failure is only in a
> printed report.
>
> I will really appreciate help with this
>
> David

> In a report I have a subreport. It works great when the subreport has 
r> ecords to display. When there are no records to display the total on 
the 
s> ubreport is blank.  Displaying the subreport by itself based on an 
empty 
t> able shows the total as "error". The source of the control being 
summed 
i> s a field in the underlying table.

> nz() does not make any difference, I have even tried "=IIf(Count(*)
=> 0,0,Sum(nz([amount])))". They all work except when there are no 
records 
t> o display. I do not have the same problem in a very similar form where 
NZ
(> ) provides a value when there are no records. The failure is only in a 
p> rinted report.

> I will really appreciate help with this 

> David

No you are all wrong. But I do think it great that we pass on our 
experiences trying to solve problems. All suggetions failed, then I found 
this one.
=[GrossPay]-IIf([srPayDeductions].Report.HasData,[srPayDeductions].Report!
TotalDeductions,0)
It seems the report "Hasdata" property has been created to cover the 
problems of controls not being formatted when there are no records in the 
underlying table. 
The report is a pay slip, the subform is a varying list of amounts a 
worker may have asked to have deducted from his pay. TotalDeductions is 
obtained by the sum function in the subform footer. The expression above 
is the control source of the net pay.
The only drawback is that when there are no deductions, the deductions 
total is blank instead of $0.00, but I will consider that a feature.
Thanks for your answers.
David