Null values in a report

  • Thread starter Thread starter Chris Tarver
  • Start date Start date
C

Chris Tarver

I am using Access 2000. I have several reports that are
slightly complicated. They are a combination of 4-5
reports in one. The problem that I am having is that when
there is not any info in one of the sub reports, the sub
report does not show up and any of the calculations that
are conected to the sub report are returned as an ERROR.
I need to find a way to have the sub reports to appear and
give a "0", so that the calculation has a Number to work
with and the report is not missing a section.
 
Check the thread from yesterday (March 16th) with a subject of "total on
report" from "Jenny". If that doesn't help, then come on back.
 
Chris said:
I am using Access 2000. I have several reports that are
slightly complicated. They are a combination of 4-5
reports in one. The problem that I am having is that when
there is not any info in one of the sub reports, the sub
report does not show up and any of the calculations that
are conected to the sub report are returned as an ERROR.
I need to find a way to have the sub reports to appear and
give a "0", so that the calculation has a Number to work
with and the report is not missing a section.


As you say, the subreport's are not there to have a value.
What you need to do is modify your calculations to take this
into account. For example, if you have a main form text box
that you want to display the sum of a total from two
subreports:

=IIf(subreportx.Report.HasData, subreportx.Report.totalx,0)
+ IIf(subreporyt.Report.HasData, subreporty.Report.totaly,0)
 
You are missing two "]"s an possibly have stuff that previous messages
didn't include. Try:

=IIF([2002 Drive Schedule].Report.HasData, [2002 Drive
Schedule].[Report]![Sum Of Actual Draw], 0) +
IIf([Club Credit Table Main Table Query].Report.HasData, [Club Credit
Table Main Table Query].[Report]![Credits_Issued Grand Total Sum], 0)

You might also want to find a better naming convention. It isn't clear from
looking at your expression if 2002 Drive Schedule is a report or subreport
or table or query or ...

--
Duane Hookom
MS Access MVP

Chris said:
=IIf([2002 Drive Schedule].[Report]![Sum Of Actual Draw.Report.HasData,
[2002 Drive Schedule].[Report]![Sum Of Actual Draw.Report.txtTotal, 0) +
IIf([Club Credit Table Main Table Query].[Report]![Credits_Issued Grand
Total Sum]
.Report.HasData, [Club Credit Table Main Table
Query].[Report]![Credits_Issued Grand Total Sum]
 
=IIF([2002 Drive Schedule].Report.HasData, [2002 Driv
Schedule].[Report]![Sum Of Actual Draw], 0)
IIf([Club Credit Table Main Table Query].Report.HasData, [Club Credi
Table Main Table Query].[Report]![Credits_Issued Grand Total Sum], 0

2002 Drive Schedule is a subreport
 
=IIf([Mobile Credits Subreport].[Report]![Totaldata].[HasData],[Mobile Credits Subreport].[Report]![Totaldata].[txtTotal],0)+IIf([Inhouse Credits Subreports].[Report]![Credits_Issued Grand Total Sum].[HasData],[Inhouse Credits Subreports].[Report]![Credits_Issued Grand Total Sum].[txtTotal],0

This is what I have changed and it still does not want to work.
 
Somehow your controls etc have changed names from earlier in this thread.
You didn't follow the syntax that both Marsh and I have suggested.

I hinted at asking you to describe which names were subreport controls but
you seem to have ignored this suggestion. If you can't figure this out, come
back with your report, subreport control, and text box names.

--
Duane Hookom
MS Access MVP

Chris said:
=IIf([Mobile Credits Subreport].[Report]![Totaldata].[HasData],[Mobile
Credits Subreport].[Report]![Totaldata].[txtTotal],0)+IIf([Inhouse Credits
Subreports].[Report]![Credits_Issued Grand Total Sum].[HasData],[Inhouse
Credits Subreports].[Report]![Credits_Issued Grand Total Sum].[txtTotal],0)
 
Mobile Credits Subreport is the subrepor
TotalData is the Textbox with the total sums of the subrepor
Inhouse Credits Subreports is the second subrepor
Credits_Issued Grand Total Sumis the textbox in the second su

There are numerous calculations in each subreport. I have to designate which I want in the equation. When using the expression buider this is what it puts.

The reason I changed teh wordage is because you said that I needed to rename things to identify what they are

I may be confused in what I am doing with this.
I used the expresion builder to add my data to your syntax, but you are saying that I have changed it. It is possible, because after looking at this for three days now it all looks the same
 
Try remove the text box reference to the immediate left of the "[HasData]".
A report/subreport has a HasData property and a text box does not have this
property. Try:

=IIf([Mobile Credits Subreport].[Report].[HasData], [Mobile Credits
Subreport].[Report]![Totaldata].[txtTotal], 0) + IIf([Inhouse Credits
Subreports].[Report].[HasData], [Inhouse Credits
Subreports].[Report]![Credits_Issued Grand Total Sum].[txtTotal], 0)

--
Duane Hookom
MS Access MVP
Chris said:
Mobile Credits Subreport is the subreport
TotalData is the Textbox with the total sums of the subreport
Inhouse Credits Subreports is the second subreport
Credits_Issued Grand Total Sumis the textbox in the second sub

There are numerous calculations in each subreport. I have to designate
which I want in the equation. When using the expression buider this is what
it puts.
The reason I changed teh wordage is because you said that I needed to
rename things to identify what they are.
I may be confused in what I am doing with this.
I used the expresion builder to add my data to your syntax, but you are
saying that I have changed it. It is possible, because after looking at
this for three days now it all looks the same
 
Back
Top