Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting in a report (2000)

    I have a report with seven columns all based on fields in a table that are yes/no fields, showing up as checkboxes on the report. The customers can order one or more of the items in those seven fields. How can I get a separate total from each column in the report footer? (And then I need to do a calculation for the fee X the number of each item ordered.)

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

    Re: Counting in a report (2000)

    Say that the first Yes/No field is named ysnItem1. Put a text box txtCountItem1 in the report footer, with control source

    =Abs(Sum([ysnItem1]))

    Repeat for the others. To calculate the total cost, create another text box with control source

    =[txtCountItem1]*[Fee1]+[txtCountItem2]*[Fee2]+...+[txtCountItem7]*[Fee7]

    where Fee1 ... Fee7 are the fees (here as fields)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting in a report (2000)

    Thanks!
    That all worked, except I forgot a couple of the fields are number fields rather than yes/no, so people can order more than one of an item. That "=Abs(Sum([ysnItem1]))" function doesn't account for all of the orders for some reason...?

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

    Re: Counting in a report (2000)

    Yes/No fields are stored internally as numbers, with True = -1 and False = 0. So if you sum a Yes/No field, all True entries count as -1 and False entries aren't counted. The Abs function makes the result positive.

    However, if you have a number field, the same expression should work, although the Abs function is superfluous then. If it doesn't work, you'll have to provide more information.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting in a report (2000)

    I got it figured out. Thanks again for your invaluable help! What type of employment do you have? Computer-related?

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

    Re: Counting in a report (2000)

    > Computer-related?

    Uh, yeah. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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