By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,052 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Old problem of totaling values from subreports on main report

P: n/a
Hi all,

I'm currently pulling out my already thin hair and need a little help
if possible. I have an Access 97 db that is used to store inspection
information for different regions. My report is meant to show the
number of inspections done in each region and what ratings they
achieved as below:

Region Green Yellow Red NR
Total
----------------------------------------------------------------------------------------------------------
North 5 1 0
12 18
South 0 6 0
9 15
East 3 3 3
1 10

Each value for green, yellow, red and NR is calculated from a "Count"
query as the data is stored as green, yellow, red or NR. The queries
are then attached to subreports which are then used in the main report
and the total is a text box adding the values contained in the
subreports. The problem as it stands is that if one of the subreports
doesn't return any value then it stays as a nul and causes an error in
my textbox calculation. Is there a way to make the subreports default
to zero instead of Null?

I've tried several things including IIf(IsNull(...) and NZ(...) but
can't seem to get this working so any help would be highly appreciated.

Many thanks, Dean...

Jan 26 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Dean,

I'm new to coding but had a similar problem.

My initial guess is that you need something like this.

Nz(Green, 0)

I know you mentioned using Nz just check your syntax. Yet, if all you
want to do is return a 0 in place of null you don't need the If
statment.

Could you give us a snippet of the code that you have been working
with?!

Hope this was helpful!

Best

Jan 26 '07 #2

P: n/a
Hi there,

Still can't seem to get it working. I've used
IIf(IsNull(Green),0,Green) and Nz(Green, 0) also IIf(Nz(Green) Is
Null),0,Green) and a number of others to no avail. I've tried altering
the query as well but with no luck. I know I've lost the plot
somewhere because other people seem to be able to get it to work no
problem.

Many thanks, Dean...

On Jan 26, 9:52 am, "hmil...@hartford.edu" <hmil...@hartford.edu>
wrote:
Dean,

I'm new to coding but had a similar problem.

My initial guess is that you need something like this.

Nz(Green, 0)

I know you mentioned using Nz just check your syntax. Yet, if all you
want to do is return a 0 in place of null you don't need the If
statment.

Could you give us a snippet of the code that you have been working
with?!

Hope this was helpful!

Best
Jan 26 '07 #3

P: n/a
Dean,

Try this, it's a long shot but...

If Len(Nz(Green, "")) = 0 Then
.....
End If

Cheers!

Jan 26 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.