Null Prob

  • Thread starter Thread starter rob p
  • Start date Start date
R

rob p

Table has an field called Amount. Long Int, Standard, 2 decimals.

Use query of same for form and resulting report. In the detail section of
the report, if there is nothing in Amount field I get error. Same with Group
footer. This report is called rptsubreport. (I eventually need to insert
into a rptmainreport. - Not until I solve this annoying problem.) Of course,
if there are numbers all works fine.

Detail: =Nz([Amount],0)

Group footer: = Nz(Sum([Amount]),0)

Thanks.
 
Either in the query criteria, under the [Amount] field place:

iif([Amount] is null, 0, [Amount])

or in a calculated field control source of the report place:

=iif([Amount] is null, 0, [Amount])

hellp? :)
 
rob said:
Table has an field called Amount. Long Int, Standard, 2 decimals.

Use query of same for form and resulting report. In the detail section of
the report, if there is nothing in Amount field I get error. Same with Group
footer. This report is called rptsubreport. (I eventually need to insert
into a rptmainreport. - Not until I solve this annoying problem.) Of course,
if there are numbers all works fine.

Detail: =Nz([Amount],0)
Click to expand...

Make sure the text box with that expression in not named
Amount. If it is change the text box's name to something
else such as txtAmount.
 
Fix one, find another: In my SQL in the subreport, I have a column -

runsubamt: Sum(DSum("amount","querytblpayeegarnish",""))

which is inserted in the subreport footer. Works if there if there are
amounts. Err if not. I put IIF([Amount] is null,0,[Amount]) in the query
criteria for Amount column.

I can't reference the txt box name to pull over to main report. Can anyone
see what I am doing wrong?
thanks.
 
Another but close problem: Did what was suggested in query criteria. I don't
get err in detail section for amount. I still get in group footer where I
want to have =Nz(Sum([Amount]),0) - what could possibly be wrong with this
code? Is there a way to display what is actually in the amount field in the
footer so I can understand err?

thanks.
 
rob said:
Fix one, find another: In my SQL in the subreport, I have a column -

runsubamt: Sum(DSum("amount","querytblpayeegarnish",""))

which is inserted in the subreport footer. Works if there if there are
amounts. Err if not. I put IIF([Amount] is null,0,[Amount]) in the query
criteria for Amount column.
Click to expand...

I am getting very confused here. What do you mean by "Err
if not"? What kind of error? Where is the error?

As a matter of fact, I don't understand how the Sum(DSum(...
can produce a meaningful result, yet you say it works??

In a normal situation (and I can't tell if what you're
trying to do is normal or not), the query would only have
the Amount field (not calculated) and the subreport would
total the amount in a footer section by using a text box
with the simple expression =Sum(Amount). If your problem is
that you want to have a text box on the main report display
the subreport's total even when the subreport has no
records, then the main report text box's expression would be
something like:

=IIf(subreport.Report.HasData,subreport.Report.totaltextbox,0)


I can't reference the txt box name to pull over to main report. Can anyone
see what I am doing wrong?
Click to expand...

What do you mean "can't reference"? What have you tried
using?

The simplest reference for a main report text box to display
a value in a subreport is =subreport.Report.totaltextbox.
The above expression can be used to avoid #Error when the
subreport might not have any data.
 
rob said:
Another but close problem: Did what was suggested in query criteria. I don't
get err in detail section for amount. I still get in group footer where I
want to have =Nz(Sum([Amount]),0) - what could possibly be wrong with this
code? Is there a way to display what is actually in the amount field in the
footer so I can understand err?
Click to expand...


That's just the wrong way to deal with the situation where
the subreport might not have any data. There is no
expression you can use in the subreport, because the
subreport is not processed when there are no records.

See my earlier reply for more details.
 
Back
Top