nz function

B

Bob

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'
 
B

Bob

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

Bob


KARL DEWEY said:
Try it this way ---
=Sum(NZ([tot_min],0))
--
KARL DEWEY
Build a little - Test a little


Bob said:
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'
Click to expand...
Click to expand...
 
K

KARL DEWEY

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.
--
KARL DEWEY
Build a little - Test a little


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

Bob


KARL DEWEY said:
Try it this way ---
=Sum(NZ([tot_min],0))
--
KARL DEWEY
Build a little - Test a little


Bob said:
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'
Click to expand...
Click to expand...
Click to expand...
 
B

Bob

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


KARL DEWEY said:
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.
--
KARL DEWEY
Build a little - Test a little


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

Bob


KARL DEWEY said:
Try it this way ---
=Sum(NZ([tot_min],0))
--
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
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'
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

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

--
KARL DEWEY
Build a little - Test a little


Bob said:
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


KARL DEWEY said:
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.
--
KARL DEWEY
Build a little - Test a little


Bob said:
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))
--
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
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'
Click to expand...
Click to expand...
Click to expand...
 
B

Bob

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


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

--
KARL DEWEY
Build a little - Test a little


Bob said:
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


KARL DEWEY said:
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.
--
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.

Bob


:

Try it this way ---
=Sum(NZ([tot_min],0))
--
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
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'
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

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.
--
KARL DEWEY
Build a little - Test a little


Bob said:
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


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

--
KARL DEWEY
Build a little - Test a little


Bob said:
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.
--
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.

Bob


:

Try it this way ---
=Sum(NZ([tot_min],0))
--
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
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'
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reports, Sub-reports running sum and no data 2
Gouping In Sub Reports Leaving Windowed Headings 2
Organizing Report Pages 5
How make a control conditionally invisible? 1
Sub report expands even though Can Grow is no 3
subreport/report total question 5
Filtering subreports on main report 0
How to address subreports properties from main report 1

Top