# Thread: Exclude Hide control from the SUM (2k)

1. ## Exclude Hide control from the SUM (2k)

I have a control in a report that it is source is sum of other control e.g X. How can i exclude the value of X
control from the sum if it is not visible . thanks in advance

new user

2. ## Re: Exclude Hide control from the SUM (2k)

If you can provide more detail, the answer can be more specific.

But, in general ... What logic do you use to determine that X is not visible? You can use that same logic to decide between 2 formulas for the Sum: with X or without X.

3. ## Re: Exclude Hide control from the SUM (2k)

this control X column in detail section in a report .the hide duplicates property of X is yes . the other control is on report footer which sum the values of X . so what i need is to exclude any X value from the sum if it is duplicate

4. ## Re: Exclude Hide control from the SUM (2k)

You can't. Sum totals the values in a control, even if the control is not visible. You'll have to either use a DCount or calculate the sum in the underlying query and reference that in the report footer.

5. ## Re: Exclude Hide control from the SUM (2k)

This is possible with a little code
Enter this declaration and two function in the Code window of the report.
<pre>Dim gTotal As Long
Dim gtmp As Long
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Field1 <> gtmp Then
gTotal = gTotal + Me.Field1
End If
gtmp = Me.Field1
End Sub
Function GetTotal()
GetTotal = gTotal
End Function</pre>

In the footer of the report, create a unbound textbox with as control source:
=GetTotal()
In the code, replace Field1 with the name of the control you want to sum.
In the declaration, replace Long by Integer, Single or Double depending of the type of numeric contained in your field

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•