473,324 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Old problem of totaling values from subreports on main report

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
3 1705
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
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
Dean,

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

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

Cheers!

Jan 26 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Don Sealer | last post by:
I have a report that includes 5 different subreports. I'd like to be able to open this report using a date function (Start Date and End Date). I'd like all five subreports to show the data from...
3
by: manning_news | last post by:
Using A2K. I've been asked to modify a report currently requiring only one date parameter to now accept a date range. The main report has 2 subreports and is not bound to a table or query. The...
2
by: Henrootje | last post by:
I have a report concerning a customer. In this report we find four subreports. They are in layout identical but data comes from different tabel. Now on the main report I count the totals of each...
2
by: AccessAl | last post by:
:confused: I have inheritted a system with reports that were probably written by a novice access programmer. My problem is a report with a dozen subreports in them. I had to add another subreport...
1
by: David | last post by:
Folks, I have four subreports in a report in the detail section. I suppose I could move them to the footer of the report. Anyway, if any of them have data, I want the main report to force a...
0
by: billygotee | last post by:
Hi, I'm having an issue with VS .NET 2k5. I have a master report that is the parent of several subreports (the report got too big to keep it all in the same report). For a while this worked...
1
by: MartinaN | last post by:
Hello, I have a MS Access 2000 question. I created a main report that consists of different subreports in the Detail section. I have a couple of queries that create tables for certain cities (the...
28
by: CindySue | last post by:
I found something similar, but can't get it to work--maybe because I'm working in a report rather than a query? I have a main report that has three subreports in it. Each subreport has a total for...
6
by: Brett Barry: Go Get Geek! | last post by:
Hello, I have a main report with a Record Source, a DateToday table, that has the current Month and Year. I have about 60 queries, each pulling different data via ODBC, that I am creating...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.