Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Formula (2000)

    I have a report where I am grouping on a field. In the fields footer, I have a formula that counts the field for example =count([ClNumb]). Now I want a formula (I guess in the same footer) that if that count = 2, I want it to give me a 1 and if not a 0. I named the text box (=count([CLNumb])) YES (for example). I used =(IIF(YES = 2,1,0)) but It doesn't work. Does anyone know a formula I could use to accomplish this.....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Formula (2000)

    Try referring to the count directly instead of indirectly:

    =IIf(Count([ClNumb])=2,1,0)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Formula

    Thanks Hans = that worked - now how do I do a sum on that field in the report footer? I named the text box YES. I used =sum([YES]) in the report footer but it is not summing correctly.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Another Formula

    As you found out before, you can't sum a control, you have to sum a field or expression. However, there is a way to do what you want:

    Create another text box with control source =IIf(Count([ClNumb])=2,1,0) and set its Running Sum property to Over Group (or Over All). Let's call this text box txtRunSum.
    Create a text box in the report footer with control source =[txtRunSum].
    Check that this text box displays the correct value when you run the report. Then go back to design view and hide txtRunSum by setting its Visible property to No.

Posting Permissions

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