hide detail if the sum of an expression is based upon detail total

[RSPenta]

I have a group
and created a sum of the details items in the group
When the total is zeroi do not want the detail to appear
thanks
Ron

 

[Ofer Cohen]

Add a text box to the header section of the group that sum the detail
section, that way you can track the Zero sum before the detailed section is
printed.

In the control source of this text box write:
=Sum(Nz([FieldName],0))

FieldName = The name of the field you sum on

Set the visible property of the text box to No.
In the OnPrint event of the header section write the code:

Me.DetailSectionName.Visible = (Me.TextBoxCreateName <> 0)

DetailSectionName = The name of the detailed section
TextBoxCreateName = The name of the text box create in the first part

 

[Marshall Barton]

I have a group
and created a sum of the details items in the group
When the total is zeroi do not want the detail to appear
thanks

If you can use the Sum aggregate function in a group header
text box named txtSum, then the group header sections format
event can use the line of code:

Me.Section(0).Visible = (Me.txtSum = 0)

If you can't use Sum, then you can not get the desired
effect because the value to test for 0 will not be
calculated until after its too late to hide them.

 

[Kae]

Hello,


My question is about Sum Column report using MS Access 2003.

I have design table, query's and now I have design the report based on the
information that I have in a Query.


The format of the column where I want to get a total value is an Long
Interger (General Number) type. However, I can see that when I apply the
txtbox in the Detail section and Run Sum to All elements in the column and I
add the txt box which will display the results in the Page Footer, it won't
display the total.
I'm not sure if this is due to a format, perhaps if works for Currency in
another report that I designed it shouldn't represent an issue when you are
trying to add just numbers.

Here is the example:
Totalhours {Field Name}
1.4
1.58333333333333
2.08333333333333
3.25
3.91666666666667
0.833333333333333
1.4
2.75
0.683333333333332
3


All this should have a total of 20.9 reflected in the Page Footer, but when
I follow the steps in the doc that you referred in this post I only get
returned the value in the last row.

I even try to trick these by poring the total sum in a query, perhaps I
can't call that field (from that query) in the report, when the actually
report is calling for a different table.

If you have any suggestions I really appreciate any input that you can
provide...

Thanks a lot,

Ps: Did you know how I can reduce the format or length in the numbers to 6
digits, I try to reformatted in the table perhaps I'm obtained that value
trough a SQL Sentenced.
here is just in case you need to look at it, so you can understand what I'm
talking about.

SELECT tbl_main.TimeBegin, tbl_main.TimeEnd,
Format((([TimeBegin]-[TimeEnd])*1440*-1)/60) AS Totalhours
FROM tbl_main;

This is how I get the numbers that I want to ADD as Total in the bottom in
the report. And I think that's why I can't change the length but I'm not sure
if there is a way around this.

Thanks again in advance,
Sincerely
Kae

 

[Kae]

I just found a solution:

I add one txtbox in the Detail Section (Report) where I put the following
information in the Control Source:

=Sum(IIf([TimeBegin] And [TimeEnd],[Totalhours],0))

and Visible properties I set it to No.

Then I add an txtbox in the Page Footer Section:

and just add udner Control Source :

=[Nameofthe Textboxthat you put in the Detail Section]

And it works...