Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    New Lounger
    Join Date
    May 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Francois

Posting Permissions

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