nz function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 26 Dec 2007 20:17 GMT

Hello, I am have a report that has several subreports in it.. There is a
summery section in the header section of the main report. However if one of
the sub reports Reports #Error that will be carried through to the summery on
the main report.

This only happens when the sub report has no data to report so the total on
the footer of the sub report show #Error as well.

I have placed =NZ(Sum([tot_min]),0) in the sub report and is still shows
#Error. I have tried the same in the Summary sectoin of the main report and
Still #Error.

What I am I doing wrong please...

Thanks In Advance
Rmr'

Reply to this Message

KARL DEWEY - 26 Dec 2007 20:58 GMT

Try it this way ---
=Sum(NZ([tot_min],0))

Signature

KARL DEWEY
Build a little - Test a little

> Hello, I am have a report that has several subreports in it.. There is a
> summery section in the header section of the main report. However if one of
[quoted text clipped - 12 lines]
> Thanks In Advance
> Rmr'

Reply to this Message

Bob - 26 Dec 2007 21:06 GMT

Hi Carl I have tried that And I still get #Error in the text box where the
sum goes.

Bob

> Try it this way ---
> =Sum(NZ([tot_min],0))
[quoted text clipped - 15 lines]
> > Thanks In Advance
> > Rmr'

Reply to this Message

KARL DEWEY - 26 Dec 2007 21:31 GMT

Go into the query for the report and see what you get there. If ok then use
that query in a totals query to sum and check again.

Signature

KARL DEWEY
Build a little - Test a little

> Hi Carl I have tried that And I still get #Error in the text box where the
> sum goes.
[quoted text clipped - 20 lines]
> > > Thanks In Advance
> > > Rmr'

Reply to this Message

Bob - 26 Dec 2007 21:35 GMT

Hi carl I have done that... The quiery returns no data as it is empty....
That is the problem I think that the report is summing an empty quiery and
the main report is including a sub report that is empty as well

This is driving me nuts.

Bob

> Go into the query for the report and see what you get there. If ok then use
> that query in a totals query to sum and check again.
[quoted text clipped - 23 lines]
> > > > Thanks In Advance
> > > > Rmr'

Reply to this Message

KARL DEWEY - 26 Dec 2007 21:40 GMT

Apply the NZ function in the query.
Just to test use ---
TestField: NZ([tot_min],1,9)

Signature

KARL DEWEY
Build a little - Test a little

> Hi carl I have done that... The quiery returns no data as it is empty....
> That is the problem I think that the report is summing an empty quiery and
[quoted text clipped - 31 lines]
> > > > > Thanks In Advance
> > > > > Rmr'

Reply to this Message

Bob - 26 Dec 2007 21:47 GMT

I used TestField: NZ([tot_min],1,9) and recieved a error "has to many
arguements"

I then did TestField: NZ([tot_min],1) and was returned nothing... The
quieries is not reporting anything because there is no data that fits the
date critira that it is being given.

Again reported nothing, completly empty.

Bob

> Apply the NZ function in the query.
> Just to test use ---
[quoted text clipped - 35 lines]
> > > > > > Thanks In Advance
> > > > > > Rmr'

Reply to this Message

KARL DEWEY - 26 Dec 2007 22:57 GMT

>>The quieries is not reporting anything because there is no data that fits

the
date critira that it is being given.
Revise your criteria to include OR Is Null and it will return records.

Signature

KARL DEWEY
Build a little - Test a little

> I used TestField: NZ([tot_min],1,9) and recieved a error "has to many
> arguements"
[quoted text clipped - 46 lines]
> > > > > > > Thanks In Advance
> > > > > > > Rmr'

Reply to this Message

Allen Browne - 27 Dec 2007 01:14 GMT

Nz() won't solve this error, because it is not to do with Null. It is caused
by the attempt to sum the non-existent text boxes in the subreport -
non-existent when the subreport has no data. The solution is therefore to
test the HasData property of the report in the subreport.

Details in:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hello, I am have a report that has several subreports in it.. There is a
> summery section in the header section of the main report. However if one
[quoted text clipped - 16 lines]
> Thanks In Advance
> Rmr'

Reply to this Message