Results 1 to 12 of 12
  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 that shows Total members per product per Client. In the Client footer I need to count the member field where members do not = 0. For instance if I have 100, 50, 0, 25. I need a count of 3. How would I count this field to not count the 0 fields. Any help would be appreciated......

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report formula (2000)

    I'm not sure where you get TotalMembers, but if it is a field in the query, you could put this as your controlsource for a textbox to get the count where TotalMembers<>0.
    =Count(iif(TotalMembers=0,Null,TotalMembers))
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Report formula (2000)

    TotalMembers is the name of the field I want to count. I used this -=Count(iff([TotalMembers]=0,Null,[TotalMembers])) but I am getting undefined function IIF in expression.

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report formula (2000)

    You've got IFF not IIF... Try change that and see what happens... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Report formula (2000)

    Thanks - it works. My eyes are starting to cross.

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Formla

    Hi Linda...

    Not sure I'm understanding here...

    Are you seeing:

    Client 1 information Count 1
    Client 1 information Count 2
    Client 1 information Count 3
    etc...
    and nothing at the end of the group?

    and you want to see....

    Client 1 information
    Client 1 information
    Client 1 information
    etc...
    and then Count after the group ends? ....Counting the number of client records?

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

    Re: Another Formla

    I have the report grouped on Client number so it is 0001 (client number)
    12345 (group number)
    12222 (group number)
    44444 (group number)

    It could be 0001 12345
    0001 12222
    0001 44444

    The next grouping is 0002

    I only want to count 0001 once and then 0002 once
    The report is counting each record like 0001 etc is next to each record.

    Is this too confusing....

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Formla

    I don't think so... <img src=/S/smile.gif border=0 alt=smile width=15 height=15> No worries... We'll work it out...
    You only lose me when you say that you are grouping on client and then tell me group numbers...
    It's almost home time for me, so it's probably me that's not thinking straight here... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    From your example you want to see the count of records (or number of groups) in 0001 when the Client Number changes... correct?
    So also from your example you would want to see...

    0001 12222
    0001 44444
    Client count = 2

    0002 12435
    0002 12222
    0002 44444
    Client count = 3

    If so, just add a text box the the Client group footer... and enter =Count([ClientNumber]) in the control source property...
    (Use your client field name...)

    Does this help?

  9. #9
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Formla

    The report is grouped on Client number. The report looks like the following ( This is not lining up right when it is posted)
    Client No Group number

    1002 12345
    22222
    55555

    1003 44444
    77777

    1004 78888
    12000
    55555

    What the report is doing when I put count([client no]) in the report footer is giving me a count of 8 (for the above example). What I want for the count of Client No is a count of 3.

    If the report was not grouped, It would look like this which is why it is giving me a count of 8

    Client No Group number

    1002 12345
    1002 22222
    1002 55555
    1003 44444
    1003 77777
    1004 78888
    1004 12000
    1004 55555

    I hope this is clearer .

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

    Re: Another Formla

    I have another Report formula that I can't figure out - How do you do a count on a Grouped field if all you want is to count the field one time instead of for each record. I hope this is clear. I have a report grouped on Client NUmber. For each Client number there could be 10 Group Numbers. I only want to count the Client number once not 10. This is probably simple but I am stumped.

    Does anyone know how to get a count of a grouped field or isn't this possible?

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Formla

    If your report is grouped on client number, what I would do is insert a text box in the client number header. In the text box type =1

    In the properties for the text box give it a name such as ClientCnt. Also set the Running Sum to Over All

    In the Report Footer add a text box. In the text box type =ClientCnt

    After you see that it is working you may want to set visible to No for the ClientCnt textbox located in the Client header so that you only see the total in the report footer.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

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

    Re: Another Formla

    Thank you so much - that is what I need.

Posting Permissions

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